package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.shsh.safemanage.model.Defect;
import com.shsh.safemanage.model.DefectType;
import com.shsh.safemanage.model.Department1;
import com.shsh.safemanage.model.Department3;
import com.shsh.safemanage.model.Other;
import com.shsh.safemanage.model.Problem;
import com.shsh.safemanage.model.ProblemDepartStatisticItem;
import com.shsh.safemanage.model.ProblemTransfer;
import com.shsh.safemanage.model.ProblemTransferFeedback;
import com.shsh.safemanage.model.Role;
import com.shsh.safemanage.model.Shutdown;
import com.shsh.safemanage.model.ShutdownType;
import com.shsh.safemanage.model.Tendency;
import com.shsh.safemanage.model.TendencyType;
import com.shsh.safemanage.model.User;
import com.shsh.safemanage.util.Constants;
import com.shsh.safemanage.util.DaoUtil;
import com.shsh.safemanage.util.Util;
import com.shsh.safemanage.util.ValueUtil;

public class ProblemDao {
	public boolean addProblem(Problem problem) {
		String sql = "insert into problem(PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, DEPARTMENT1_ID, "
				+ "EQUIPMENT_ID, WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, WRITER_USER_MANAGE_GROUP_ID, WRITE_USER_DEPARTMENT1_ID, "
				+ "WRITE_USER_DEPARTMENT2_ID, IS_SOLVED) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " + "select @@identity as PROBLEM_ID";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstm.setString(1, problem.getProblemTitle());
			pstm.setString(2, problem.getProblemContent());
			pstm.setString(3, problem.getProblemZhenggai());
			pstm.setLong(4, problem.getDepartment1ID());
			pstm.setLong(5, problem.getEquipmentID());
			pstm.setLong(6, problem.getWriteUserID());
			pstm.setTimestamp(7, new Timestamp(problem.getWriteTime().getTimeInMillis()));
			pstm.setInt(8, problem.getProcessStatus());
			if (problem.getWriterManageGroupID() != null) {
				pstm.setLong(9, problem.getWriterManageGroupID());
			} else {
				pstm.setNull(9, Types.INTEGER);
			}

			if (problem.getWriterDepartment1ID() != null) {
				pstm.setLong(10, problem.getWriterDepartment1ID());
			} else {
				pstm.setNull(10, Types.INTEGER);
			}

			if (problem.getWriterDepartment2ID() != null) {
				pstm.setLong(11, problem.getWriterDepartment2ID());
			} else {
				pstm.setNull(11, Types.INTEGER);
			}

			pstm.setBoolean(12, problem.isSolved());

			pstm.executeUpdate();
			ResultSet rs = pstm.getGeneratedKeys();
			if (rs.next()) {
				problem.setId(rs.getLong("PROBLEM_ID"));
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean addPostcheckProblem(Problem problem) {
		String sql = "insert into problem(PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, DEPARTMENT1_ID, "
				+ "EQUIPMENT_ID, WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, PROBLEM_TYPE, "
				+ "POSTCHECK_ID, POSTCHECK_PATTERN_ID, POSTCHECK_CONTENT_ID, POSTCHECK_STANDARD_ID, GRADE, COMPLETE_DATE, WRITE_USER_DEPARTMENT1_ID, WRITE_USER_DEPARTMENT2_ID, "
				+ " CLOSE_USER_ID, CLOSE_TIME, IS_SOLVED, IS_SANWEI) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " + "select @@identity as PROBLEM_ID";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstm.setString(1, problem.getProblemTitle());
			pstm.setString(2, problem.getProblemContent());
			pstm.setString(3, problem.getProblemZhenggai());
			pstm.setLong(4, problem.getDepartment1ID());
			pstm.setLong(5, problem.getEquipmentID());
			pstm.setLong(6, problem.getWriteUserID());
			pstm.setTimestamp(7, new Timestamp(problem.getWriteTime().getTimeInMillis()));
			pstm.setInt(8, problem.getProcessStatus());

			pstm.setInt(9, problem.getProblemType());

			if (problem.getPostcheckID() == null) {
				pstm.setNull(10, Types.BIGINT);
			} else {
				pstm.setLong(10, problem.getPostcheckID());
			}

			if (problem.getPostcheckPatternID() == null) {
				pstm.setNull(11, Types.BIGINT);
			} else {
				pstm.setLong(11, problem.getPostcheckPatternID());
			}

			if (problem.getPostcheckContentID() == null) {
				pstm.setNull(12, Types.BIGINT);
			} else {
				pstm.setLong(12, problem.getPostcheckContentID());
			}

			if (problem.getPostcheckStandardID() == null) {
				pstm.setNull(13, Types.BIGINT);
			} else {
				pstm.setLong(13, problem.getPostcheckStandardID());
			}

			if (problem.getGrade() == null) {
				pstm.setNull(14, Types.INTEGER);
			} else {
				pstm.setInt(14, problem.getGrade());
			}
			pstm.setTimestamp(15, new Timestamp(problem.getCompleteDate().getTimeInMillis()));

			if (problem.getWriterDepartment1ID() == null) {
				pstm.setNull(16, Types.BIGINT);
			} else {
				pstm.setLong(16, problem.getWriterDepartment1ID());
			}

			if (problem.getWriterDepartment2ID() == null) {
				pstm.setNull(17, Types.BIGINT);
			} else {
				pstm.setLong(17, problem.getWriterDepartment2ID());
			}

			if (problem.getCloseUserID() == null) {
				pstm.setNull(18, Types.BIGINT);
			} else {
				pstm.setLong(18, problem.getCloseUserID());
			}

			if (problem.getCloseTime() == null) {
				pstm.setNull(19, Types.TIMESTAMP);
			} else {
				pstm.setTimestamp(19, new Timestamp(problem.getCloseTime().getTimeInMillis()));
			}

			pstm.setBoolean(20, problem.isSolved());
			if (problem.isSanwei() == null) {
				pstm.setNull(21, Types.BOOLEAN);
			} else {
				pstm.setBoolean(21, problem.isSanwei());
			}

			pstm.executeUpdate();
			ResultSet rs = pstm.getGeneratedKeys();
			if (rs.next()) {
				problem.setId(rs.getLong("PROBLEM_ID"));
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean addProblemTransfers(List<ProblemTransfer> transferList) {
		String sql = "insert into problem_transfer([USER_ID], PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, IS_MAIN_TRANSFERED_USER, "
				+ "IS_FIRST_SENT) values(?, ?, ?, ?, ?, ?)";
		String existsSQL = "select ID from problem_transfer where PROBLEM_ID=? and TRANSFERED_USER_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(existsSQL);

			for (ProblemTransfer transfer : transferList) {
				pstm1.setLong(1, transfer.getProblemID());
				pstm1.setLong(2, transfer.getTransferedUserID());

				ResultSet rs1 = pstm1.executeQuery();
				if (!rs1.next()) {
					pstm.setLong(1, transfer.getUserID());
					pstm.setLong(2, transfer.getProblemID());
					pstm.setLong(3, transfer.getTransferedUserID());
					pstm.setBoolean(4, Boolean.FALSE);
					pstm.setBoolean(5, transfer.isMainTransferUser());
					pstm.setBoolean(6, transfer.isFirstSent());

					pstm.executeUpdate();
				}
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public Map<String, Object> searchNormalProblems(Long department1ID, Long equipmentID, Long writeUserManageGroupID, String problemTitle,
			String problemContent, String dateStart, String dateEnd, Integer processStatus, Long writerDepartment1ID,
			Long writerDepartment2ID, int pageNum) {
		String sql = "select problem.ID, PROBLEM_TYPE, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "inner join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "inner join equipment on problem.EQUIPMENT_ID=equipment.ID where problem.PROBLEM_TYPE is null ";

		boolean hasPre = true;

		if (department1ID != null) {
			if (hasPre) {
				sql += " and ";
			}
			sql += "problem.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (writeUserManageGroupID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITER_USER_MANAGE_GROUP_ID=" + writeUserManageGroupID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}

		if (processStatus != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROCESS_STATUS>=" + processStatus + " ";
			hasPre = true;
		}

		if (writerDepartment1ID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "(employee.DEPARTMENT1_ID=" + writerDepartment1ID + " or " + "WRITE_USER_DEPARTMENT1_ID=" + writerDepartment1ID + ") ";
			hasPre = true;
		}

		if (writerDepartment2ID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "(employee.DEPARTMENT2_ID=" + writerDepartment2ID + " or " + "WRITE_USER_DEPARTMENT2_ID=" + writerDepartment2ID + ") ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put(Constants.DAO_RESULT_NUMBER, DaoUtil.getCount(sql, new int[] {}, new Object[] {}));
		sql = DaoUtil.getPagingSQL(sql, pageNum, Constants.PAGE_SIZE, "WRITE_TIME", false);

		List<Problem> problemList = new ArrayList<Problem>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setProblemType(rs.getInt("PROBLEM_TYPE"));

				problemList.add(problem);
			}
			resultMap.put(Constants.DAO_RESULT, problemList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Map<String, Object> searchProblems(Long department1ID, Long equipmentID, Long writeUserManageGroupID, String problemTitle,
			String problemContent, String dateStart, String dateEnd, Integer processStatus, Long writerDepartment1ID,
			Long writerDepartment2ID, Integer problemType, int roleLevel, int page, int pageSize) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_TYPE, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME, "
				+ "role.ROLE_LEVEL, role.ROLE_TYPE "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "inner join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join equipment on problem.EQUIPMENT_ID=equipment.ID " + "inner join role on department3.ROLE_ID=role.ID where ";
		Map<String, Object> resultMap = new HashMap<String, Object>(2);
		resultMap.put(Constants.DAO_RESULT, new ArrayList<Problem>(0));
		resultMap.put(Constants.DAO_RESULT_NUMBER, 0);

		boolean hasPre = false;

		if (department1ID != null) {
			if (hasPre) {
				sql += " and ";
			}
			sql += "problem.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (writeUserManageGroupID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITER_USER_MANAGE_GROUP_ID=" + writeUserManageGroupID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}

		if (processStatus != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROCESS_STATUS=" + processStatus + " ";
			hasPre = true;
		}

		if (writerDepartment1ID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "(employee.DEPARTMENT1_ID=" + writerDepartment1ID + " or " + "WRITE_USER_DEPARTMENT1_ID=" + writerDepartment1ID + ") ";
			hasPre = true;
		}

		if (writerDepartment2ID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "(employee.DEPARTMENT2_ID=" + writerDepartment2ID + " or " + "WRITE_USER_DEPARTMENT2_ID=" + writerDepartment2ID + ") ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		if (problemType != null) {
			if (hasPre) {
				sql += "and ";
			}
			if (problemType.equals(0)) {
				sql += "PROBLEM_TYPE is null ";
			} else {
				sql += "PROBLEM_TYPE=" + problemType + " ";
			}
		}

		if (roleLevel > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "role.ROLE_LEVEL>=" + roleLevel + " ";
		}

		int totalNum = DaoUtil.getCount(sql, new int[] {}, new Object[] {});
		if (roleLevel == 0) {
			sql = DaoUtil.getPagingSQL(sql, page, pageSize, "WRITE_TIME", false);
		} else if (roleLevel > 0) {
			sql = DaoUtil.getPagingSQL(sql, page, pageSize, new String[] { "ROLE_LEVEL", "ROLE_TYPE", "NAME", "WRITE_TIME" },
					new Boolean[] { Boolean.FALSE, Boolean.FALSE, Boolean.TRUE, Boolean.FALSE });
		}

		List<Problem> problemList = new ArrayList<Problem>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setProblemType(rs.getInt("PROBLEM_TYPE"));
				problem.setEquipmentName(ValueUtil.getString(rs.getObject("EQUIP_NAME")));

				problemList.add(problem);
			}

			resultMap.put(Constants.DAO_RESULT, problemList);
			resultMap.put(Constants.DAO_RESULT_NUMBER, totalNum);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Map<String, Object> searchPostchecks(Long postcheckID, Long equipmentID, Long postcheckContentID, String problemTitle,
			String problemContent, String dateStart, String dateEnd, int page) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, "
				+ " equipment.EQUIP_NAME, problem.COMPLETE_DATE, ce.NAME as CLOSE_USER_NAME, GRADE, wd1.DEPARTMENT1_NAME as WRITER_DEPARTMENT1_NAME "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "left outer join department1 as wd1 on wd1.ID = employee.DEPARTMENT1_ID "
				+ "left outer join employee as ce on ce.ID = problem.CLOSE_USER_ID "
				+ "left outer join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "left outer join equipment on problem.EQUIPMENT_ID=equipment.ID " + "where GRADE > 0 and ";

		boolean hasPre = false;

		sql += (" PROBLEM_TYPE = " + Constants.PROBLEM_TYPE_POSTCHECK + " ");
		if (postcheckID != null) {
			sql += "  and POSTCHECK_ID = " + postcheckID + " ";
		}

		hasPre = true;

		if (equipmentID != null && equipmentID != 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}
		if (postcheckContentID != null && postcheckContentID != 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "POSTCHECK_CONTENT_ID=" + postcheckContentID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put(Constants.DAO_RESULT_NUMBER, DaoUtil.getCount(sql, new int[] {}, new Object[] {}));

		String pagingSQL = DaoUtil.getPagingSQL(sql, page, Constants.PAGE_SIZE, "WRITE_TIME", false);

		List<Problem> problemList = new ArrayList<Problem>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(pagingSQL);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setCompleteDate(ValueUtil.getCalendar(rs.getObject("COMPLETE_DATE")));
				problem.setCloseUserName(rs.getString("CLOSE_USER_NAME"));
				problem.setGrade(rs.getInt("GRADE"));
				problem.setWriterDepartment1Name(rs.getString("WRITER_DEPARTMENT1_NAME"));

				problemList.add(problem);
			}

			resultMap.put(Constants.DAO_RESULT, problemList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Map<String, Object> searchPostchecks(Long postcheckID, Long department1ID, Long equipmentID, Long postcheckContentID,
			String problemTitle, String problemContent, String dateStart, String dateEnd, Long writerDepartment1ID, int pageNum) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, "
				+ " equipment.EQUIP_NAME, problem.COMPLETE_DATE, ce.NAME as CLOSE_USER_NAME, GRADE, wd1.DEPARTMENT1_NAME as WRITER_DEPARTMENT1_NAME  "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID ";
		if (department1ID != null && department1ID != 0) {
			sql += " and employee.DEPARTMENT1_ID=" + department1ID + " ";
		}

		sql += "left outer join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "left outer join department1 as wd1 on wd1.ID = employee.DEPARTMENT1_ID "
				+ " left outer join employee as ce on ce.ID = problem.CLOSE_USER_ID "
				+ "left outer join equipment on problem.EQUIPMENT_ID=equipment.ID " + "where GRADE > 0 and ";

		sql += (" PROBLEM_TYPE = " + Constants.PROBLEM_TYPE_POSTCHECK + " ");

		if (postcheckID != null) {
			sql += "  and POSTCHECK_ID = " + postcheckID + " ";
		}

		if (equipmentID != null && equipmentID != 0) {
			sql += " and EQUIPMENT_ID=" + equipmentID + " ";
		}

		if (postcheckContentID != null && postcheckContentID != 0) {
			sql += " and POSTCHECK_CONTENT_ID=" + postcheckContentID + " ";
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			sql += " and PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			sql += " and PROBLEM_CONTENT like '%" + problemContent + "%' ";
		}

		if (writerDepartment1ID != null) {
			sql += " and (employee.DEPARTMENT1_ID=" + writerDepartment1ID + " or " + "WRITE_USER_DEPARTMENT1_ID=" + writerDepartment1ID
					+ ") ";
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			sql += " and WRITE_TIME>='" + dateStart + " 00:00:00' ";
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			sql += " and WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		}

		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put(Constants.DAO_RESULT_NUMBER, DaoUtil.getCount(sql, new int[] {}, new Object[] {}));
		sql = DaoUtil.getPagingSQL(sql, pageNum, Constants.PAGE_SIZE, "WRITE_TIME", false);

		List<Problem> problemList = new ArrayList<Problem>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setCompleteDate(ValueUtil.getCalendar(rs.getObject("COMPLETE_DATE")));
				problem.setCloseUserName(rs.getString("CLOSE_USER_NAME"));
				problem.setGrade(rs.getInt("GRADE"));
				problem.setWriterDepartment1Name(rs.getString("WRITER_DEPARTMENT1_NAME"));

				problemList.add(problem);
			}

			resultMap.put(Constants.DAO_RESULT, problemList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Map<String, Object> searchDefects(Long department1ID, Long equipmentID, Long defectTypeID, String problemTitle,
			String problemContent, String dateStart, String dateEnd, int page) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME, "
				+ " defect.DEFECT_TYPE_ID, defect.LOSS, defect.REASON, dt.DEFECT_TYPE "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "left outer join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "left outer join equipment on problem.EQUIPMENT_ID=equipment.ID " + " left outer join defect on defect.ID = problem.ID "
				+ "left outer join defect_type as dt on dt.ID = defect.DEFECT_TYPE_ID " + " where PROBLEM_TYPE = "
				+ Constants.PROBLEM_TYPE_DEFECT + " and PROCESS_STATUS >= " + Constants.PROBLEM_STATUS_CLOSE + " ";

		boolean hasPre = true;

		if (department1ID != null && department1ID > 0) {
			if (hasPre) {
				sql += " and ";
			}
			sql += "problem.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null && equipmentID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (defectTypeID != null && defectTypeID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "defect.DEFECT_TYPE_ID=" + defectTypeID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put(Constants.DAO_RESULT_NUMBER, DaoUtil.getCount(sql, new int[] {}, new Object[] {}));
		sql = DaoUtil.getPagingSQL(sql, page, Constants.PAGE_SIZE, "WRITE_TIME desc, ID ", false);

		List<Defect> problemList = new ArrayList<Defect>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Defect problem = new Defect();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));

				problem.setDefectTypeID(rs.getInt("DEFECT_TYPE_ID"));
				problem.setLoss(rs.getString("LOSS"));
				problem.setReason(rs.getString("REASON"));

				problem.setDefectType(rs.getString("DEFECT_TYPE"));

				problemList.add(problem);
			}

			resultMap.put(Constants.DAO_RESULT, problemList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Map<String, Object> searchTendencys(Long department1ID, Long equipmentID, Long tendencyTypeID, String problemTitle,
			String problemContent, String dateStart, String dateEnd, int page) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME, "
				+ " tendency.TENDENCY_TYPE_ID, tendency.LOSS, tendency.REASON, tt.TENDENCY_TYPE "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "left outer join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "left outer join equipment on problem.EQUIPMENT_ID=equipment.ID "
				+ " left outer join tendency on tendency.ID = problem.ID left outer join tendency_type as tt on tt.ID = tendency.TENDENCY_TYPE_ID "
				+ " where PROBLEM_TYPE = " + Constants.PROBLEM_TYPE_TENDENCY + " and PROCESS_STATUS >= " + Constants.PROBLEM_STATUS_CLOSE
				+ " ";

		boolean hasPre = true;

		if (department1ID != null && department1ID > 0) {
			if (hasPre) {
				sql += " and ";
			}
			sql += "problem.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null && equipmentID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (tendencyTypeID != null && tendencyTypeID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "tendency.TENDENCY_TYPE_ID=" + tendencyTypeID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put(Constants.DAO_RESULT_NUMBER, DaoUtil.getCount(sql, new int[] {}, new Object[] {}));
		sql = DaoUtil.getPagingSQL(sql, page, Constants.PAGE_SIZE, "WRITE_TIME", false);

		List<Tendency> problemList = new ArrayList<Tendency>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Tendency problem = new Tendency();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));

				problem.setTendencyTypeID(rs.getInt("TENDENCY_TYPE_ID"));
				problem.setLoss(rs.getString("LOSS"));
				problem.setReason(rs.getString("REASON"));
				problem.setTendencyType(rs.getString("TENDENCY_TYPE"));

				problemList.add(problem);
			}

			resultMap.put(Constants.DAO_RESULT, problemList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Map<String, Object> searchShutdowns(Integer problemTypeID, Long department1ID, Long equipmentID, Long shutdownTypeID,
			Boolean escalateZongbu, String problemTitle, String problemContent, String dateStart, String dateEnd, int page) {

		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME, "
				+ " shut.SHUTDOWN_TYPE_ID, shut.LOSS, shut.REASON, dt.SHUTDOWN_TYPE "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "left outer join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "left outer join equipment on problem.EQUIPMENT_ID=equipment.ID "
				+ " left outer join  [shutdown] as shut on shut.ID = problem.ID "
				+ "left outer join shutdown_type as dt on dt.ID = shut.SHUTDOWN_TYPE_ID " + " where PROBLEM_TYPE = " + problemTypeID
				+ " and PROCESS_STATUS >= " + Constants.PROBLEM_STATUS_CLOSE + " ";

		boolean hasPre = true;

		if (department1ID != null && department1ID > 0) {
			if (hasPre) {
				sql += " and ";
			}
			sql += "problem.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null && equipmentID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (shutdownTypeID != null && shutdownTypeID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "shut.SHUTDOWN_TYPE_ID=" + shutdownTypeID + " ";
			hasPre = true;
		}

		if (escalateZongbu != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "shut.ESCALATE_ZONGBU=" + (escalateZongbu ? "1" : "0") + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put(Constants.DAO_RESULT_NUMBER, DaoUtil.getCount(sql, new int[] {}, new Object[] {}));
		sql = DaoUtil.getPagingSQL(sql, page, Constants.PAGE_SIZE, " WRITE_TIME desc, ID ", false);

		List<Shutdown> problemList = new ArrayList<Shutdown>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Shutdown problem = new Shutdown();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));

				problem.setShutdownTypeID(rs.getInt("SHUTDOWN_TYPE_ID"));
				problem.setLoss(rs.getString("LOSS"));
				problem.setReason(rs.getString("REASON"));

				problem.setShutdownType(rs.getString("SHUTDOWN_TYPE"));

				problemList.add(problem);
			}

			resultMap.put(Constants.DAO_RESULT, problemList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Problem getProblemByID(Long id, Integer problemType) {
		if (problemType == null || problemType.equals(Constants.PROBLEM_TYPE_POSTCHECK)) {
			return this.getProblemByID(id);
		} else if (problemType.equals(Constants.PROBLEM_TYPE_DEFECT)) {
			return this.getDefectByID(id);
		} else if (problemType.equals(Constants.PROBLEM_TYPE_NOTPLANED_SHUTDOWN)
				|| problemType.equals(Constants.PROBLEM_TYPE_TEMP_SHUTDOWN)) {
			return this.getShutdownByID(id);
		} else if (problemType.equals(Constants.PROBLEM_TYPE_TENDENCY)) {
			return this.getTendencyByID(id);
		}

		return null;
	}

	public Problem getProblemByID(Long id) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, PROBLEM_TYPE, POSTCHECK_ID, POSTCHECK_PATTERN_ID, POSTCHECK_CONTENT_ID, POSTCHECK_STANDARD_ID, "
				+ "GRADE, COMPLETE_DATE, ESCALATE_DEPART1_ID, CLOSE_USER_ID, CLOSE_TIME, PROCESSING_BY_CHUSHI, IS_SOLVED, "
				+ "employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "left outer join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "left outer join equipment on problem.EQUIPMENT_ID=equipment.ID where problem.ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setProblemType(rs.getInt("PROBLEM_TYPE"));
				problem.setPostcheckID(ValueUtil.getLong(rs.getObject("POSTCHECK_ID")));
				problem.setPostcheckPatternID(ValueUtil.getLong(rs.getObject("POSTCHECK_PATTERN_ID")));
				problem.setPostcheckContentID(ValueUtil.getLong(rs.getObject("POSTCHECK_CONTENT_ID")));
				problem.setPostcheckStandardID(ValueUtil.getLong(rs.getObject("POSTCHECK_STANDARD_ID")));
				Object gradeObj = rs.getObject("GRADE");
				if (gradeObj != null) {
					problem.setGrade((Integer) gradeObj);
				}
				problem.setCompleteDate(ValueUtil.getCalendar(rs.getObject("COMPLETE_DATE")));
				problem.setEscalateDepart1ID(ValueUtil.getLong(rs.getObject("ESCALATE_DEPART1_ID")));
				problem.setCloseUserID(ValueUtil.getLong(rs.getObject("CLOSE_USER_ID")));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));
				problem.setSolved(ValueUtil.getBoolean(rs.getBoolean("IS_SOLVED")));

				return problem;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public Defect getDefectByID(Long problemID) {
		Problem problem = getProblemByID(problemID);

		String sql = " select d.ID, DEFECT_TYPE_ID, LOSS, REASON, DEFECT_TYPE, DUTY, TEAM, DEFECT_TIME, DEFECT_WRITE_TIME, DEFECT_WRITE_USER_ID "
				+ "from defect as d left outer join defect_type as dt on dt.ID = d.DEFECT_TYPE_ID where d.ID = ? ";

		Connection conn = null;
		PreparedStatement pstm = null;

		Defect defect = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();

			if (rs.next()) {
				defect = new Defect(problem);
				defect.setId(rs.getLong("ID"));
				defect.setDefectTypeID(rs.getInt("DEFECT_TYPE_ID"));
				defect.setDefectType(rs.getString("DEFECT_TYPE"));
				defect.setLoss(rs.getString("LOSS"));
				defect.setReason(rs.getString("REASON"));
				defect.setDuty(rs.getString("DUTY"));
				defect.setTeam(rs.getString("TEAM"));
				defect.setDefectTime(ValueUtil.getCalendar(rs.getObject("DEFECT_TIME")));
				defect.setDefectWriteTime(ValueUtil.getCalendar(rs.getObject("DEFECT_WRITE_TIME")));
				Object obj = rs.getObject("DEFECT_WRITE_USER_ID");
				if (obj != null) {
					defect.setDefectWriteUserID(Long.valueOf((Integer) obj));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return defect;
	}

	public Shutdown getShutdownByID(Long problemID) {
		Problem problem = getProblemByID(problemID);

		String sql = " select d.ID, SHUTDOWN_TYPE_ID, LOSS, REASON, SHUTDOWN_TYPE, DUTY, TEAM, PERIOD_START, PERIOD_END, SHUTDOWN_WRITE_TIME, SHUTDOWN_WRITE_USER_ID, ESCALATE_ZONGBU "
				+ "from  [shutdown] as d " + " left outer join shutdown_type as dt on dt.ID = d.SHUTDOWN_TYPE_ID where d.ID = ? ";

		Connection conn = null;
		PreparedStatement pstm = null;

		Shutdown shutdown = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();

			if (rs.next()) {
				shutdown = new Shutdown(problem);
				shutdown.setId(rs.getLong("ID"));
				shutdown.setShutdownTypeID(rs.getInt("SHUTDOWN_TYPE_ID"));
				shutdown.setShutdownType(rs.getString("SHUTDOWN_TYPE"));
				shutdown.setLoss(rs.getString("LOSS"));
				shutdown.setReason(rs.getString("REASON"));
				shutdown.setDuty(rs.getString("DUTY"));
				shutdown.setTeam(rs.getString("TEAM"));
				shutdown.setPeriodStart(ValueUtil.getCalendar(rs.getObject("PERIOD_START")));
				shutdown.setPeriodEnd(ValueUtil.getCalendar(rs.getObject("PERIOD_END")));
				shutdown.setShutdownWriteTime(ValueUtil.getCalendar(rs.getObject("SHUTDOWN_WRITE_TIME")));
				if (rs.getObject("ESCALATE_ZONGBU") != null) {
					shutdown.setEscalateZongbu(rs.getBoolean("ESCALATE_ZONGBU"));
				} else {
					shutdown.setEscalateZongbu(null);
				}

				Object obj = rs.getObject("SHUTDOWN_WRITE_USER_ID");
				if (obj != null) {
					shutdown.setShutdownWriteUserID(Long.valueOf((Integer) obj));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return shutdown;
	}

	public Tendency getTendencyByID(Long problemID) {
		Problem problem = getProblemByID(problemID);

		String sql = " select d.ID, TENDENCY_TYPE_ID, LOSS, REASON, TENDENCY_TYPE, DUTY, TEAM, TENDENCY_TIME, TENDENCY_WRITE_TIME, TENDENCY_WRITE_USER_ID "
				+ " from tendency as d left outer join tendency_type as dt on dt.ID = d.TENDENCY_TYPE_ID where d.ID = ? ";

		Connection conn = null;
		PreparedStatement pstm = null;

		Tendency tendency = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();

			if (rs.next()) {
				tendency = new Tendency(problem);
				tendency.setId(rs.getLong("ID"));
				tendency.setTendencyTypeID(rs.getInt("TENDENCY_TYPE_ID"));
				tendency.setTendencyType(rs.getString("TENDENCY_TYPE"));
				tendency.setLoss(rs.getString("LOSS"));
				tendency.setReason(rs.getString("REASON"));
				tendency.setDuty(rs.getString("DUTY"));
				tendency.setTeam(rs.getString("TEAM"));
				tendency.setTendencyTime(ValueUtil.getCalendar(rs.getObject("TENDENCY_TIME")));
				tendency.setTendencyWriteTime(ValueUtil.getCalendar(rs.getObject("TENDENCY_WRITE_TIME")));
				Object obj = rs.getObject("TENDENCY_WRITE_USER_ID");
				if (obj != null) {
					tendency.setTendencyWriteUserID(Long.valueOf((Integer) obj));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return tendency;
	}

	public List<ProblemTransfer> getProblemTransfer(Long problemID, Long transferedUserID) {
		String sql = "select problem_transfer.ID, USER_ID, PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, "
				+ "PROCESS_TIME, employee.NAME, employee.DEPARTMENT1_ID, IS_NEW, IS_FIRST_SENT "
				+ "from problem_transfer left outer join employee on problem_transfer.TRANSFERED_USER_ID=employee.ID "
				+ "where PROBLEM_ID=? and TRANSFERED_USER_ID = ? order by PROCESS_TIME asc";

		String feedbackSQL = "select ID, PROCESS_USER_ID, TRANSFER_ID, PROBLEM_ID, "
				+ "TRANSFER_FEEDBACK, FEEDBACK_TIME, PROCESS_METHOD, TRANSFERED_USER_NAMES from problem_transfer_feedback "
				+ "where TRANSFER_ID=? order by FEEDBACK_TIME asc";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		ProblemTransfer transfer = null;
		List<ProblemTransfer> transferList = new ArrayList<ProblemTransfer>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(feedbackSQL);

			pstm.setLong(1, problemID);
			pstm.setLong(2, transferedUserID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				transfer = new ProblemTransfer();
				transfer.setId(rs.getLong("ID"));
				transfer.setUserID(rs.getLong("USER_ID"));
				transfer.setProblemID(rs.getLong("PROBLEM_ID"));
				transfer.setTransferedUserID(rs.getLong("TRANSFERED_USER_ID"));
				transfer.setProcessed(rs.getBoolean("IS_PROCESSED"));
				transfer.setProcessTime(ValueUtil.getCalendar(rs.getObject("PROCESS_TIME")));
				transfer.setTransferedUserName(rs.getString("NAME"));
				transfer.setTransferedUserDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				transfer.setNew(rs.getBoolean("IS_NEW"));
				transfer.setFirstSent(rs.getBoolean("IS_FIRST_SENT"));

				pstm1.setLong(1, transfer.getId());
				ResultSet rs1 = pstm1.executeQuery();
				List<ProblemTransferFeedback> feedbackList = new ArrayList<ProblemTransferFeedback>();
				while (rs1.next()) {
					ProblemTransferFeedback feedback = new ProblemTransferFeedback();
					feedback.setId(rs1.getLong("ID"));
					feedback.setUserID(rs1.getLong("PROCESS_USER_ID"));
					feedback.setTransferID(rs1.getLong("TRANSFER_ID"));
					feedback.setProblemID(rs1.getLong("PROBLEM_ID"));
					feedback.setFeedback(rs1.getString("TRANSFER_FEEDBACK"));
					feedback.setFeedbackTime(ValueUtil.getCalendar(rs1.getObject("FEEDBACK_TIME")));
					feedback.setProcessMethod(rs1.getInt("PROCESS_METHOD"));
					feedback.setTransferedUserNames(rs1.getString("TRANSFERED_USER_NAMES"));

					feedbackList.add(feedback);
				}
				transfer.setFeedbackList(feedbackList);

				transferList.add(transfer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return transferList;
	}

	public ProblemTransfer getProblemTransfer(Long writeUserID, Long problemID, Long transferedUserID) {
		String sql = "select problem_transfer.ID, USER_ID, PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, "
				+ "PROCESS_TIME, employee.NAME, employee.DEPARTMENT1_ID, IS_NEW, IS_FIRST_SENT "
				+ "from problem_transfer left outer join employee on problem_transfer.TRANSFERED_USER_ID=employee.ID "
				+ "where USER_ID = ? and PROBLEM_ID=? and TRANSFERED_USER_ID = ? order by PROCESS_TIME asc";
		String feedbackSQL = "select ID, PROCESS_USER_ID, TRANSFER_ID, PROBLEM_ID, "
				+ "TRANSFER_FEEDBACK, FEEDBACK_TIME, PROCESS_METHOD, TRANSFERED_USER_NAMES from problem_transfer_feedback "
				+ "where TRANSFER_ID=? order by FEEDBACK_TIME asc";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		ProblemTransfer transfer = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(feedbackSQL);

			pstm.setLong(1, writeUserID);
			pstm.setLong(2, problemID);
			pstm.setLong(3, transferedUserID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				transfer = new ProblemTransfer();
				transfer.setId(rs.getLong("ID"));
				transfer.setUserID(rs.getLong("USER_ID"));
				transfer.setProblemID(rs.getLong("PROBLEM_ID"));
				transfer.setTransferedUserID(rs.getLong("TRANSFERED_USER_ID"));
				transfer.setProcessed(rs.getBoolean("IS_PROCESSED"));
				transfer.setProcessTime(ValueUtil.getCalendar(rs.getObject("PROCESS_TIME")));
				transfer.setTransferedUserName(rs.getString("NAME"));
				transfer.setTransferedUserDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				transfer.setNew(rs.getBoolean("IS_NEW"));
				transfer.setFirstSent(rs.getBoolean("IS_FIRST_SENT"));

				pstm1.setLong(1, transfer.getId());
				ResultSet rs1 = pstm1.executeQuery();
				List<ProblemTransferFeedback> feedbackList = new ArrayList<ProblemTransferFeedback>();
				while (rs1.next()) {
					ProblemTransferFeedback feedback = new ProblemTransferFeedback();
					feedback.setId(rs1.getLong("ID"));
					feedback.setUserID(rs1.getLong("PROCESS_USER_ID"));
					feedback.setTransferID(rs1.getLong("TRANSFER_ID"));
					feedback.setProblemID(rs1.getLong("PROBLEM_ID"));
					feedback.setFeedback(rs1.getString("TRANSFER_FEEDBACK"));
					feedback.setFeedbackTime(ValueUtil.getCalendar(rs1.getObject("FEEDBACK_TIME")));
					feedback.setProcessMethod(rs1.getInt("PROCESS_METHOD"));
					feedback.setTransferedUserNames(rs1.getString("TRANSFERED_USER_NAMES"));

					feedbackList.add(feedback);
				}
				transfer.setFeedbackList(feedbackList);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return transfer;
	}

	public List<ProblemTransfer> getProblemTransferByProblemID(Long problemID) {
		String sql = "select problem_transfer.ID, USER_ID, PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, "
				+ "PROCESS_TIME, employee.NAME, employee.DEPARTMENT1_ID, IS_NEW, IS_FIRST_SENT "
				+ "from problem_transfer left outer join employee on problem_transfer.TRANSFERED_USER_ID=employee.ID "
				+ "where PROBLEM_ID=? order by PROCESS_TIME asc";
		String feedbackSQL = "select ID, PROCESS_USER_ID, TRANSFER_ID, PROBLEM_ID, "
				+ "TRANSFER_FEEDBACK, FEEDBACK_TIME, PROCESS_METHOD, TRANSFERED_USER_NAMES from problem_transfer_feedback "
				+ "where TRANSFER_ID=? order by FEEDBACK_TIME asc";

		List<ProblemTransfer> transferList = new ArrayList<ProblemTransfer>();

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(feedbackSQL);

			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProblemTransfer transfer = new ProblemTransfer();
				transfer.setId(rs.getLong("ID"));
				transfer.setUserID(rs.getLong("USER_ID"));
				transfer.setProblemID(rs.getLong("PROBLEM_ID"));
				transfer.setTransferedUserID(rs.getLong("TRANSFERED_USER_ID"));
				transfer.setProcessed(rs.getBoolean("IS_PROCESSED"));
				transfer.setProcessTime(ValueUtil.getCalendar(rs.getObject("PROCESS_TIME")));
				transfer.setTransferedUserName(rs.getString("NAME"));
				transfer.setTransferedUserDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				transfer.setNew(rs.getBoolean("IS_NEW"));
				transfer.setFirstSent(rs.getBoolean("IS_FIRST_SENT"));

				pstm1.setLong(1, transfer.getId());
				ResultSet rs1 = pstm1.executeQuery();
				List<ProblemTransferFeedback> feedbackList = new ArrayList<ProblemTransferFeedback>();
				while (rs1.next()) {
					ProblemTransferFeedback feedback = new ProblemTransferFeedback();
					feedback.setId(rs1.getLong("ID"));
					feedback.setUserID(rs1.getLong("PROCESS_USER_ID"));
					feedback.setTransferID(rs1.getLong("TRANSFER_ID"));
					feedback.setProblemID(rs1.getLong("PROBLEM_ID"));
					feedback.setFeedback(rs1.getString("TRANSFER_FEEDBACK"));
					feedback.setFeedbackTime(ValueUtil.getCalendar(rs1.getObject("FEEDBACK_TIME")));
					feedback.setProcessMethod(rs1.getInt("PROCESS_METHOD"));
					feedback.setTransferedUserNames(rs1.getString("TRANSFERED_USER_NAMES"));

					feedbackList.add(feedback);
				}
				transfer.setFeedbackList(feedbackList);

				transferList.add(transfer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return transferList;
	}

	public List<ProblemTransfer> getProblemTransferListByForwardTransferID(Long forwardTransferID) {
		String sql = "select problem_transfer.ID, USER_ID, PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, "
				+ "PROCESS_TIME, employee.NAME, IS_NEW, IS_FIRST_SENT "
				+ "from problem_transfer left outer join employee on problem_transfer.TRANSFERED_USER_ID=employee.ID "
				+ "where FORWARD_MAPPING_TRANSFER_ID=?";

		List<ProblemTransfer> transferList = new ArrayList<ProblemTransfer>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, forwardTransferID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProblemTransfer transfer = new ProblemTransfer();
				transfer.setId(rs.getLong("ID"));
				transfer.setUserID(rs.getLong("USER_ID"));
				transfer.setProblemID(rs.getLong("TRANSFERED_USER_ID"));
				transfer.setProcessed(rs.getBoolean("IS_PROCESSED"));
				transfer.setProcessTime(ValueUtil.getCalendar(rs.getObject("PROCESS_TIME")));
				transfer.setTransferedUserName(rs.getString("NAME"));

				transferList.add(transfer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return transferList;
	}

	public List<Problem> getDepart1ProblemsTobeProcessed(Long depart1ID, int beforeStatus, String startDate, String endDate) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_TYPE, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_DEPARTMENT1_ID, WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, "
				+ "department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME, PROCESSING_BY_CHUSHI, ESCALATE_DEPART1_ID "
				+ "from problem inner join employee on problem.WRITE_USER_ID=employee.ID "
				+ "inner join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "inner join equipment on problem.EQUIPMENT_ID=equipment.ID where PROCESS_STATUS<? and problem.DEPARTMENT1_ID=? ";
		if (!Util.isNullOrEmpty(startDate)) {
			sql += "and WRITE_TIME>='" + startDate + " 00:00:00' ";
		}
		if (!Util.isNullOrEmpty(endDate)) {
			sql += "and WRITE_TIME<='" + endDate + " 23:59:59' ";
		}
		sql += "order by WRITE_TIME desc";

		List<Problem> problemList = new ArrayList<Problem>();

		UserDao userDao = DaoCreator.createUserDao();
		DepartmentDao departmentDao = DaoCreator.createDepartmentDao();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, beforeStatus);
			pstm.setLong(2, depart1ID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriterDepartment1ID(rs.getLong("WRITE_USER_DEPARTMENT1_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setProblemType(rs.getInt("PROBLEM_TYPE"));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));
				problem.setEscalateDepart1ID(rs.getLong("ESCALATE_DEPART1_ID"));

				User writerUser = userDao.getUserByID(problem.getWriteUserID());
				Department1 depart1 = departmentDao.getDepartment1ByID(writerUser.getDepartment1ID());
				problem.setWriterDepartmentType(depart1.getDepartmentType());

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> getProblemsTobeProcessed(Long userID) {
		String sql = "select problem_transfer.ID, problem_transfer.PROBLEM_ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, "
				+ "problem.DEPARTMENT1_ID, EQUIPMENT_ID, department1.DEPARTMENT1_NAME, EQUIP_NAME, problem.PROBLEM_TYPE, "
				+ "WRITE_USER_ID, WRITE_USER_DEPARTMENT1_ID, WRITE_TIME, PROCESS_STATUS, employee.NAME, IS_NEW, PROCESSING_BY_CHUSHI, ESCALATE_DEPART1_ID "
				+ "from problem_transfer " + "inner join problem on problem_transfer.PROBLEM_ID=problem.ID "
				+ "inner join employee on problem.WRITE_USER_ID=employee.ID "
				+ "inner join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "inner join equipment on problem.EQUIPMENT_ID=equipment.ID "
				+ "where problem_transfer.TRANSFERED_USER_ID=? and PROCESS_STATUS<>" + Constants.PROBLEM_STATUS_CLOSE + " "
				+ "and PROCESS_STATUS<>" + Constants.PROBLEM_STATUS_PROVED + " " + "order by WRITE_TIME desc";
		List<Problem> problemList = new ArrayList<Problem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);

			DepartmentDao departmentDao = DaoCreator.createDepartmentDao();
			UserDao userDao = DaoCreator.createUserDao();

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setTransferID(rs.getLong("ID"));
				problem.setId(rs.getLong("PROBLEM_ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriterDepartment1ID(rs.getLong("WRITE_USER_DEPARTMENT1_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setNew(rs.getBoolean("IS_NEW"));
				problem.setProblemType(rs.getInt("PROBLEM_TYPE"));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));
				problem.setEscalateDepart1ID(rs.getLong("ESCALATE_DEPART1_ID"));

				User writerUser = userDao.getUserByID(problem.getWriteUserID());
				Department1 depart1 = departmentDao.getDepartment1ByID(writerUser.getDepartment1ID());
				problem.setWriterDepartmentType(depart1.getDepartmentType());

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> getProblemsTobeTendency(Long userID) {
		String sql = "select p.ID, p.DEPARTMENT1_ID, d1.DEPARTMENT1_NAME, p.EQUIPMENT_ID, d2.DEPARTMENT2_NAME as EQUIP_NAME, "
				+ "p.PROBLEM_TITLE, p.PROBLEM_CONTENT, p.PROBLEM_ZHENGGAI_CUOSHI, p.PROCESSING_BY_CHUSHI, e.NAME, p.WRITE_TIME, "
				+ "PROCESS_STATUS, p.WRITE_USER_ID " + "from problem as p left outer join department1 as d1 on p.DEPARTMENT1_ID = d1.ID "
				+ "left outer join department2 as d2 on p.EQUIPMENT_ID = d2.EQUIP_ID "
				+ "left outer join employee as e on e.ID = p.WRITE_USER_ID where p.WRITE_USER_ID = ? and p.PROBLEM_TYPE is null "
				+ "order by p.WRITE_TIME desc ";
		List<Problem> problemList = new ArrayList<Problem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> getProblemsTobeDefect(Long userID) {
		String sql = "select p.ID, p.DEPARTMENT1_ID, d1.DEPARTMENT1_NAME, p.EQUIPMENT_ID, d2.DEPARTMENT2_NAME as EQUIP_NAME, "
				+ "p.PROBLEM_TITLE, p.PROBLEM_CONTENT, p.PROBLEM_ZHENGGAI_CUOSHI, e.NAME, p.WRITE_TIME, PROCESS_STATUS, p.WRITE_USER_ID, PROCESSING_BY_CHUSHI "
				+ "from problem as p left outer join department1 as d1 on p.DEPARTMENT1_ID = d1.ID "
				+ "left outer join department2 as d2 on p.EQUIPMENT_ID = d2.EQUIP_ID "
				+ "left outer join employee as e on e.ID = p.WRITE_USER_ID where p.WRITE_USER_ID = ? and p.PROBLEM_TYPE is null "
				+ "order by p.WRITE_TIME desc ";
		List<Problem> problemList = new ArrayList<Problem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> getProblemsTobeConverted(Long userID) {
		String sql = "select p.ID, p.DEPARTMENT1_ID, d1.DEPARTMENT1_NAME, p.EQUIPMENT_ID, d2.DEPARTMENT2_NAME as EQUIP_NAME, "
				+ "p.PROBLEM_TITLE, p.PROBLEM_CONTENT, p.PROBLEM_ZHENGGAI_CUOSHI, e.NAME, p.WRITE_TIME, PROCESS_STATUS, p.WRITE_USER_ID, p.PROCESSING_BY_CHUSHI "
				+ "from problem as p left outer join department1 as d1 on p.DEPARTMENT1_ID = d1.ID "
				+ "left outer join department2 as d2 on p.EQUIPMENT_ID = d2.EQUIP_ID "
				+ "left outer join employee as e on e.ID = p.WRITE_USER_ID where p.WRITE_USER_ID = ? and p.PROBLEM_TYPE is null "
				+ "order by p.WRITE_TIME desc ";
		List<Problem> problemList = new ArrayList<Problem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setProcessingByChushi(rs.getBoolean("PROCESSING_BY_CHUSHI"));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public boolean isMainProblemProcessor(Long transferedUserID, Long problemID, Long transferID) {
		String sql = "select ID from problem_transfer where TRANSFERED_USER_ID=? and PROBLEM_ID=? and ID=? "
				+ "and IS_PROCESSED=0 and IS_MAIN_TRANSFERED_USER=1";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, transferedUserID);
			pstm.setLong(2, problemID);
			pstm.setLong(3, transferID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public ProblemTransfer getProblemTransferByID(Long id) {
		String sql = "select ID, USER_ID, PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, "
				+ "PROCESS_TIME, IS_MAIN_TRANSFERED_USER from problem_transfer where ID=?";
		String feedbackSQL = "select ID, PROCESS_USER_ID, TRANSFER_ID, PROBLEM_ID, TRANSFER_FEEDBACK, FEEDBACK_TIME, "
				+ "PROCESS_METHOD from problem_transfer_feedback where TRANSFER_ID=? order by FEEDBACK_TIME asc";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm1 = conn.prepareStatement(feedbackSQL);

			pstm.setLong(1, id);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemTransfer transfer = new ProblemTransfer();
				transfer.setId(rs.getLong("ID"));
				transfer.setUserID(rs.getLong("USER_ID"));
				transfer.setProblemID(rs.getLong("PROBLEM_ID"));
				transfer.setTransferedUserID(rs.getLong("TRANSFERED_USER_ID"));
				transfer.setProcessed(rs.getBoolean("IS_PROCESSED"));
				transfer.setProcessTime(ValueUtil.getCalendar(rs.getObject("PROCESS_TIME")));
				transfer.setMainTransferUser(rs.getBoolean("IS_MAIN_TRANSFERED_USER"));

				pstm1.setLong(1, transfer.getId());
				ResultSet rs1 = pstm1.executeQuery();
				List<ProblemTransferFeedback> feedbackList = new ArrayList<ProblemTransferFeedback>();
				while (rs1.next()) {
					ProblemTransferFeedback feedback = new ProblemTransferFeedback();
					feedback.setId(rs1.getLong("ID"));
					feedback.setUserID(rs1.getLong("PROCESS_USER_ID"));
					feedback.setTransferID(rs1.getLong("TRANSFER_ID"));
					feedback.setProblemID(rs1.getLong("PROBLEM_ID"));
					feedback.setFeedback(rs1.getString("TRANSFER_FEEDBACK"));
					feedback.setFeedbackTime(ValueUtil.getCalendar(rs1.getObject("FEEDBACK_TIME")));
					feedback.setProcessMethod(rs1.getInt("PROCESS_METHOD"));

					feedbackList.add(feedback);
				}
				transfer.setFeedbackList(feedbackList);

				return transfer;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<Long> getTransferRoleUserByTransferedUserID(Long problemID, int roleType) {
		String sql = "select distinct employee.ID from problem_transfer "
				+ "left outer join employee on problem_transfer.USER_ID=employee.ID "
				+ "left outer join job_post on employee.JOB_POST1_ID=job_post.ID or employee.JOB_POST2_ID=job_post.ID or employee.JOB_POST2_ID=job_post.ID "
				+ "left outer join role on job_post.ROLE_ID=role.ID " + "where problem_transfer.PROBLEM_ID=? and (role.ROLE_TYPE & ?) > 0";
		List<Long> userIDList = new ArrayList<Long>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);
			pstm.setInt(2, roleType);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				userIDList.add(rs.getLong("ID"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return userIDList;
	}

	public boolean processProblem(String feedback, Long transferID, Long problemID, Long userID, int status, int processMethod,
			List<ProblemTransfer> transferList, Long escalateDepart1ID, Integer grade, boolean processingByChushi) {
		String sql1 = "update problem_transfer set PROCESS_TIME=?, IS_PROCESSED=1 where ID=?";
		String sql2 = "update problem set PROCESS_STATUS=?, CLOSE_USER_ID=?, CLOSE_TIME=?, ESCALATE_DEPART1_ID=?, GRADE=?, PROCESSING_BY_CHUSHI=? where ID=?";
		String sql3 = "update problem_transfer set IS_PROCESSED=1 where PROBLEM_ID=?";
		String sql4 = "insert into problem_transfer_feedback(TRANSFER_ID, PROBLEM_ID, TRANSFER_FEEDBACK, FEEDBACK_TIME, PROCESS_METHOD, "
				+ "PROCESS_USER_ID, TRANSFERED_USER_NAMES) values(?, ?, ?, ?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstm1 = null;
		PreparedStatement pstm2 = null;
		PreparedStatement pstm3 = null;
		PreparedStatement pstm4 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);

			pstm1 = conn.prepareStatement(sql1);
			pstm2 = conn.prepareStatement(sql2);
			pstm3 = conn.prepareStatement(sql3);
			pstm4 = conn.prepareStatement(sql4);

			pstm1.setTimestamp(1, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			pstm1.setLong(2, transferID);

			pstm2.setInt(1, status);
			if (status == Constants.PROBLEM_STATUS_CLOSE || status == Constants.PROBLEM_STATUS_PROVED) {
				pstm2.setLong(2, userID);
				pstm2.setTimestamp(3, new Timestamp(Calendar.getInstance().getTimeInMillis()));
				if (escalateDepart1ID == null) {
					pstm2.setNull(4, Types.INTEGER);
				} else {
					pstm2.setLong(4, escalateDepart1ID);
				}
			} else {
				pstm2.setNull(2, Types.INTEGER);
				pstm2.setNull(3, Types.TIMESTAMP);
				if (escalateDepart1ID == null) {
					pstm2.setNull(4, Types.INTEGER);
				} else {
					pstm2.setLong(4, escalateDepart1ID);
				}
			}
			if (grade == null) {
				pstm2.setNull(5, Types.INTEGER);
			} else {
				pstm2.setInt(5, grade);
			}
			pstm2.setBoolean(6, processingByChushi);
			pstm2.setLong(7, problemID);

			pstm3.setLong(1, problemID);

			pstm4.setLong(1, transferID);
			pstm4.setLong(2, problemID);
			pstm4.setString(3, feedback);
			pstm4.setTimestamp(4, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			pstm4.setInt(5, processMethod);
			pstm4.setLong(6, userID);
			StringBuilder transferedNames = new StringBuilder();
			if (transferList != null && transferList.size() > 0) {
				UserDao userDao = DaoCreator.createUserDao();
				for (ProblemTransfer transfer : transferList) {
					User user = userDao.getUserByID(transfer.getTransferedUserID());
					if (transferedNames.length() == 0) {
						transferedNames.append(user.getName());
					} else {
						transferedNames.append(" " + user.getName());
					}
				}
			}
			pstm4.setString(7, transferedNames.toString());

			pstm1.executeUpdate();
			pstm2.executeUpdate();
			pstm4.executeUpdate();
			if (status == Constants.PROBLEM_STATUS_CLOSE) {
				pstm3.executeUpdate();
			}

			conn.commit();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public ProblemTransferFeedback getLatestProblemTransferFeedbackByProblemID(Long problemID) {
		String sql = "select ID, PROCESS_USER_ID, TRANSFER_ID, PROBLEM_ID, TRANSFER_FEEDBACK, FEEDBACK_TIME, PROCESS_METHOD, TRANSFERED_USER_NAMES "
				+ "from problem_transfer_feedback where PROBLEM_ID=? and FEEDBACK_TIME=(select max(FEEDBACK_TIME) from problem_transfer_feedback where PROBLEM_ID=?) "
				+ "order by ID desc";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);
			pstm.setLong(2, problemID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemTransferFeedback feedback = new ProblemTransferFeedback();
				feedback.setId(rs.getLong("ID"));
				feedback.setUserID(rs.getLong("PROCESS_USER_ID"));
				feedback.setTransferID(rs.getLong("TRANSFER_ID"));
				feedback.setProblemID(rs.getLong("PROBLEM_ID"));
				feedback.setFeedback(rs.getString("TRANSFER_FEEDBACK"));
				feedback.setFeedbackTime(ValueUtil.getCalendar(rs.getObject("FEEDBACK_TIME")));
				feedback.setProcessMethod(rs.getInt("PROCESS_METHOD"));
				feedback.setTransferedUserNames(rs.getString("TRANSFERED_USER_NAMES"));

				return feedback;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<ProblemTransferFeedback> getProblemTransferFeedbackByProblemID(Long problemID) {
		String sql = "select ID, PROCESS_USER_ID, TRANSFER_ID, PROBLEM_ID, TRANSFER_FEEDBACK, FEEDBACK_TIME, PROCESS_METHOD, TRANSFERED_USER_NAMES "
				+ "from problem_transfer_feedback where PROBLEM_ID=? order by FEEDBACK_TIME asc";

		List<ProblemTransferFeedback> feedbackList = new ArrayList<ProblemTransferFeedback>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProblemTransferFeedback feedback = new ProblemTransferFeedback();
				feedback.setId(rs.getLong("ID"));
				feedback.setUserID(rs.getLong("PROCESS_USER_ID"));
				feedback.setTransferID(rs.getLong("TRANSFER_ID"));
				feedback.setProblemID(rs.getLong("PROBLEM_ID"));
				feedback.setFeedback(rs.getString("TRANSFER_FEEDBACK"));
				feedback.setFeedbackTime(ValueUtil.getCalendar(rs.getObject("FEEDBACK_TIME")));
				feedback.setProcessMethod(rs.getInt("PROCESS_METHOD"));
				feedback.setTransferedUserNames(rs.getString("TRANSFERED_USER_NAMES"));

				feedbackList.add(feedback);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return feedbackList;
	}

	public List<Problem> getUserRelatedProblemList(Long userID, Long userDepart2ID, Long userDepart3ID, String startDate, String endDate) {
		String problemIDSQL = "select distinct PROBLEM_ID from problem_transfer where USER_ID=? and IS_FIRST_SENT=1";
		String problemIDSQL1 = "select distinct PROBLEM_ID from problem_transfer where TRANSFERED_USER_ID=?";
		String problemIDSQL2 = "select distinct problem_transfer.PROBLEM_ID from problem_transfer "
				+ "inner join employee on employee.ID=problem_transfer.USER_ID or employee.ID=problem_transfer.TRANSFERED_USER_ID "
				+ "where employee.DEPARTMENT2_ID=?";
		String problemIDSQL3 = "select distinct problem_transfer.PROBLEM_ID from problem_transfer "
				+ "inner join employee on employee.ID=problem_transfer.USER_ID "
				+ "inner join department2 on employee.DEPARTMENT2_ID=department2.ID and department2.IS_MANAGE_GROUP=1 "
				+ "inner join user_managegroup_mapping on department2.DEPARTMENT1_ID=user_managegroup_mapping.DEPARTMENT1_ID "
				+ "and department2.MANAGE_GROUP_ID=user_managegroup_mapping.MANAGE_GROUP_ID " + "where user_managegroup_mapping.USER_ID=?";
		String problemIDSQL4 = "select distinct problem_transfer.PROBLEM_ID from problem_transfer "
				+ "inner join problem on problem_transfer.PROBLEM_ID=problem.ID "
				+ "inner join user_equip_mapping on problem.EQUIPMENT_ID=user_equip_mapping.CORRESPONDED_EQUIPMENT_ID "
				+ "where user_equip_mapping.USER_ID=?";

		String getProblemSQL1 = "select ID, PROBLEM_TITLE, PROBLEM_TYPE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, DEPARTMENT1_ID, "
				+ "EQUIPMENT_ID, WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME from problem "
				+ "where problem.ID in :ID and PROCESS_STATUS>=? and WRITE_TIME>='" + startDate + "' and WRITE_TIME<='" + endDate + "' "
				+ "order by WRITE_TIME desc";
		String getProblemSQL2 = "select problem.ID, PROBLEM_TITLE, PROBLEM_TYPE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, "
				+ "problem.EQUIPMENT_ID, WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME from problem "
				+ "inner join employee on problem.WRITE_USER_ID=employee.ID "
				+ "inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join role on department3.ROLE_ID=role.ID "
				+ "where problem.ID in :ID and PROCESS_STATUS>=? and WRITE_TIME>='"
				+ startDate
				+ "' and WRITE_TIME<='"
				+ endDate
				+ "' "
				+ "order by WRITE_TIME desc, role.ROLE_TYPE asc";

		List<Problem> problemList = new ArrayList<Problem>();

		Connection conn = null;
		PreparedStatement problemIDSQLPstm = null;
		PreparedStatement problemIDSQL1Pstm = null;
		PreparedStatement problemIDSQL2Pstm = null;
		PreparedStatement problemIDSQL3Pstm = null;
		PreparedStatement problemIDSQL4Pstm = null;

		PreparedStatement getProblemSQLPstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();

			problemIDSQLPstm = conn.prepareStatement(problemIDSQL);
			problemIDSQL1Pstm = conn.prepareStatement(problemIDSQL1);
			problemIDSQL2Pstm = conn.prepareStatement(problemIDSQL2);
			problemIDSQL3Pstm = conn.prepareStatement(problemIDSQL3);
			problemIDSQL4Pstm = conn.prepareStatement(problemIDSQL4);

			Set<Long> problemIDSet = new HashSet<Long>();

			DepartmentDao departDao = DaoCreator.createDepartmentDao();
			TitlePostManageGroupDao tpmDao = DaoCreator.createTitlePostManageGroupDao();
			Department3 depart3 = departDao.getDepartment3ByID(userDepart3ID);
			Role role = tpmDao.getRoleByID(depart3.getRoleID());

			if ((role.getRoleType() & Constants.ROLE_TYPE_YIXIANYUANGONG) > 0) {
				problemIDSQLPstm.setLong(1, userID);
				ResultSet rs = problemIDSQLPstm.executeQuery();
				while (rs.next()) {
					problemIDSet.add(rs.getLong("PROBLEM_ID"));
				}
			} else if ((role.getRoleType() & (Constants.ROLE_TYPE_ZUYUAN | Constants.ROLE_TYPE_ANQUANZONGJIAN
					| Constants.ROLE_TYPE_SHENGCHANFUBUZHANG | Constants.ROLE_TYPE_SHEBEIFUBUZHANG | Constants.ROLE_TYPE_BUZHANG
					| Constants.ROLE_TYPE_KEYUAN | Constants.ROLE_TYPE_CHUZHANG | Constants.ROLE_TYPE_FUCHUZHANG)) > 0) {
				problemIDSQLPstm.setLong(1, userID);
				ResultSet rs = problemIDSQLPstm.executeQuery();
				while (rs.next()) {
					problemIDSet.add(rs.getLong("PROBLEM_ID"));
				}

				problemIDSQL1Pstm.setLong(1, userID);
				rs = problemIDSQL1Pstm.executeQuery();
				while (rs.next()) {
					problemIDSet.add(rs.getLong("PROBLEM_ID"));
				}

				if ((role.getRoleType() & (Constants.ROLE_TYPE_ANQUANZONGJIAN | Constants.ROLE_TYPE_SHENGCHANFUBUZHANG
						| Constants.ROLE_TYPE_SHEBEIFUBUZHANG | Constants.ROLE_TYPE_KEYUAN | Constants.ROLE_TYPE_FUCHUZHANG)) > 0) {
					problemIDSQL3Pstm.setLong(1, userID);
					rs = problemIDSQL3Pstm.executeQuery();
					while (rs.next()) {
						problemIDSet.add(rs.getLong("PROBLEM_ID"));
					}
				}

				if ((role.getRoleType() & (Constants.ROLE_TYPE_KEYUAN | Constants.ROLE_TYPE_FUCHUZHANG)) > 0) {
					problemIDSQL4Pstm.setLong(1, userID);
					rs = problemIDSQL4Pstm.executeQuery();
					while (rs.next()) {
						problemIDSet.add(rs.getLong("PROBLEM_ID"));
					}
				}
			} else if ((role.getRoleType() & (Constants.ROLE_TYPE_ZUZHANG | Constants.ROLE_TYPE_KEZHANG | Constants.ROLE_TYPE_FUKEZHANG | Constants.ROLE_TYPE_ZHUANGZHIZHANG)) > 0) {
				problemIDSQL2Pstm.setLong(1, userDepart2ID);
				ResultSet rs = problemIDSQL2Pstm.executeQuery();
				while (rs.next()) {
					problemIDSet.add(rs.getLong("PROBLEM_ID"));
				}

				if ((role.getRoleType() & (Constants.ROLE_TYPE_KEZHANG | Constants.ROLE_TYPE_FUKEZHANG)) > 0) {
					problemIDSQL3Pstm.setLong(1, userID);
					rs = problemIDSQL3Pstm.executeQuery();
					while (rs.next()) {
						problemIDSet.add(rs.getLong("PROBLEM_ID"));
					}

					problemIDSQL4Pstm.setLong(1, userID);
					rs = problemIDSQL4Pstm.executeQuery();
					while (rs.next()) {
						problemIDSet.add(rs.getLong("PROBLEM_ID"));
					}
				}
			}

			StringBuilder problemIDStrBuilder = new StringBuilder();
			for (Long problemID : problemIDSet) {
				if (problemIDStrBuilder.length() > 0) {
					problemIDStrBuilder.append("," + problemID);
				} else {
					problemIDStrBuilder.append(problemID);
				}
			}

			String problemIDStr = null;
			if (problemIDStrBuilder.length() == 0) {
				problemIDStr = "(0)";
			} else {
				problemIDStr = "(" + problemIDStrBuilder.toString() + ")";
			}

			if ((role.getRoleType() & Constants.ROLE_TYPE_ZHUANGZHIZHANG) > 0) {
				getProblemSQL2 = getProblemSQL2.replace(":ID", problemIDStr);
				getProblemSQLPstm = conn.prepareStatement(getProblemSQL2);
			} else {
				getProblemSQL1 = getProblemSQL1.replace(":ID", problemIDStr);
				getProblemSQLPstm = conn.prepareStatement(getProblemSQL1);
			}
			getProblemSQLPstm.setInt(1, Constants.PROBLEM_STATUS_CLOSE);

			ResultSet rs = getProblemSQLPstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setProblemType(rs.getInt("PROBLEM_TYPE"));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public boolean deleteProblemByIDList(Long[] IDList) {
		if (IDList == null || IDList.length == 0) {
			return true;
		}

		String inClause = "(" + Util.toString(IDList, ",", "") + ")";
		// Column names
		String problemColumns = "[ID],[PROBLEM_TITLE],[PROBLEM_CONTENT],[PROBLEM_ZHENGGAI_CUOSHI] ,[DEPARTMENT1_ID] ,[EQUIPMENT_ID]  ,[WRITE_USER_ID],[WRITE_TIME],[PROCESS_STATUS] ,[CLOSE_USER_ID] ,[CLOSE_TIME] ,[PROBLEM_TYPE]  ,[GRADE]  ,[POSTCHECK_ID],[COMPLETE_DATE] ,[POSTCHECK_PATTERN_ID] ,[POSTCHECK_CONTENT_ID] ,[POSTCHECK_STANDARD_ID] ,[ESCALATE_DEPART1_ID] , [WRITER_USER_MANAGE_GROUP_ID] ,[WRITE_USER_DEPARTMENT1_ID] ,[WRITE_USER_DEPARTMENT2_ID], [PROCESSING_BY_CHUSHI], [IS_SOLVED], [IS_SANWEI]";
		String problemTransferColumns = "[ID] ,[USER_ID] ,[PROBLEM_ID] ,[TRANSFERED_USER_ID] ,[FORWARD_MAPPING_TRANSFER_ID] ,[IS_PROCESSED] ,[PROCESS_TIME]  ,[IS_MAIN_TRANSFERED_USER],[IS_NEW],[IS_FIRST_SENT]";
		String problemTransferFeedbackColumns = "[ID]  ,[PROCESS_USER_ID]  ,[TRANSFER_ID]  ,[PROBLEM_ID]  ,[TRANSFER_FEEDBACK]  , [FEEDBACK_TIME]  ,[PROCESS_METHOD]  ,[TRANSFERED_USER_NAMES]";
		String defectColumns = "[ID] ,[DEFECT_TYPE_ID]  ,[LOSS] ,[REASON] ,[DUTY] ,[TEAM] ,[DEFECT_TIME] ,[DEFECT_WRITE_TIME] ,[DEFECT_WRITE_USER_ID]";
		String otherColumns = "[ID] ,[DEFECT_TYPE_ID]  ,[LOSS] ,[REASON] ,[DUTY] ,[TEAM] ,[DEFECT_TIME] ,[DEFECT_WRITE_TIME] ,[DEFECT_WRITE_USER_ID]";
		String tendencyColumns = "[ID] ,[TENDENCY_TYPE_ID] ,[LOSS] ,[REASON]  ,[DUTY] ,[TEAM] ,[TENDENCY_TIME] ,[TENDENCY_WRITE_TIME] ,[TENDENCY_WRITE_USER_ID]";
		String shutdownColumns = "[ID] ,[SHUTDOWN_TYPE_ID] ,[LOSS] ,[REASON] ,[DUTY] ,[TEAM]  ,[PERIOD_START] ,[PERIOD_END] ,[SHUTDOWN_WRITE_TIME] ,[SHUTDOWN_WRITE_USER_ID], [ESCALATE_ZONGBU]";
		String HSEProblemColumns = "[PROBLEM_ID] ,[HSE_CATEGORY_ID] ,[HSE_CATEGORY_TYPE] ,[DIRECT_CAUSE_ID] ,[REMOTE_CAUSE_ID] ,[OTHER]";
		String postcheckCheckReportColumns = "[ID] ,[PROBLEM_ID] ,[YEAR] ,[MONTH] ,[DEPARTMENT1_ID] ,[CHECK_REPORT]";
		String postcheckSanweiReportColumns = "[ID] ,[PROBLEM_ID] ,[YEAR]  ,[MONTH]  ,[DEPARTMENT1_ID] ,[SANWEI_REPORT]";
		String weakMonthReportColumns = "[ID] ,[YEAR] ,[MONTH] ,[DEPARTMENT1_ID] ,[EQUIP_ID] ,[USER_ID] ,[PROBLEM_ID] ,[REPORT_TYPE] ,[WEAK_REPORT]";

		String accidentCheckformColumns = "[ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_NAME] ,[ACCIDENT_STATUS]  ,[CASUALTY] ,[LOSS] ,[OVERVIEW] ,[REASON] ,[DUTY]  ,[TEAM]  ,[MORAL] ,[INTROSPECTION]  ,[REVIEW_DETAIL]  ,[REVIEW_TEAM] ,[REVIEW_TIME] ,[ACCIDENT_WRITE_TIME]  ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID], [SENDBACK_MSG]";
		String accidentReportColumns = "[ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_NAME] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,[CASUALTY] ,[LOSS] ,[OVERVIEW] ,[REASON]  ,[DUTY] ,[TEAM] ,[MORAL] ,[INTROSPECTION] ,[REVIEW_DETAIL] ,[REVIEW_TEAM] ,[REVIEW_TIME] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID], [SENDBACK_MSG]";
		String accidentQuickreportColumns = "[ID] ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_STATUS] ,[ACCIDENT_TIME] ,[ACCIDENT_NAME] ,[LOSS]  ,[OVERVIEW] ,[HANDLE] ,[REASON] ,[MORAL] ,[CASUALTY] ,[SERIOUS_INJURY_COUNT] ,[DEATH_COUNT] ,[ACCIDENT_WRITE_USER_ID]  ,[ACCIDENT_WRITE_TIME], [FACADE_ID], [SENDBACK_MSG]";
		String accidentInfosheetColumns = "[ID]  ,[ACCIDENT_TYPE_ID] ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,[ACCIDENT_NAME] ,[LOSS] ,[OVERVIEW] ,[HANDLE] ,[REASON] ,[MORAL] ,[CASUALTY] ,[SERIOUS_INJURY_COUNT] ,[DEATH_COUNT] ,[ACCIDENT_WRITE_TIME] ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID], [SENDBACK_MSG]";
		String accidentRegisterformColumns = "[ID]  ,[ACCIDENT_TYPE_ID]  ,[ACCIDENT_TIME] ,[ACCIDENT_STATUS] ,[ACCIDENT_NAME] ,[OVERVIEW]  ,[CASUALTY]  ,[LOSS] ,[HANDLE] ,[ACCIDENT_WRITE_TIME]  ,[ACCIDENT_WRITE_USER_ID], [FACADE_ID]";
		String accidentCasualtyColumns = "[ID] ,[ACCIDENT_ID] ,[NAME] ,[GENDER] ,[AGE] ,[WORK_AGE] ,[WORK_TYPE] ,[CASUALTY_DETAIL] ,[SERIOUS_LEVEL]";

		// Move data to [deleted] tables
		String sqlMoveProblem = "insert into deleted_problem (" + problemColumns + ") select " + problemColumns
				+ " from problem where ID in " + inClause;
		String sqlMoveProblemTransfer = "insert into deleted_problem_transfer(" + problemTransferColumns + ") select "
				+ problemTransferColumns + " from problem_transfer where PROBLEM_ID in " + inClause;
		String sqlMoveProblemTransferFeedBack = "insert into deleted_problem_transfer_feedback(" + problemTransferFeedbackColumns
				+ ") select " + problemTransferFeedbackColumns + " from problem_transfer_feedback where PROBLEM_ID in " + inClause;

		String sqlMoveDefect = "insert into deleted_defect(" + defectColumns + ") select " + defectColumns + " from defect where ID in "
				+ inClause;
		String sqlMoveOther = "insert into deleted_other(" + otherColumns + ") select " + otherColumns + " from other where ID in "
				+ inClause;
		String sqlMoveTendency = "insert into deleted_tendency(" + tendencyColumns + ") select " + tendencyColumns
				+ " from tendency where ID in " + inClause;
		String sqlMoveShutdown = "insert into deleted_shutdown(" + shutdownColumns + ") select " + shutdownColumns
				+ "from  [shutdown] where ID in " + inClause;
		String sqlMoveHSEProblem = "insert into deleted_problem_hse_category(" + HSEProblemColumns + ") select " + HSEProblemColumns
				+ " from problem_hse_category where PROBLEM_ID in " + inClause;
		String sqlMovePostcheckCheckReport = " insert into deleted_postcheck_month_check_report(" + postcheckCheckReportColumns
				+ ") select " + postcheckCheckReportColumns + " from postcheck_month_check_report where PROBLEM_ID in " + inClause;
		String sqlMovePostcheckSanweiReport = " insert into deleted_postcheck_month_sanwei_report (" + postcheckSanweiReportColumns
				+ ") select " + postcheckSanweiReportColumns + " from postcheck_month_sanwei_report where PROBLEM_ID in " + inClause;
		String sqlMoveWeakMonthReport = "insert into deleted_weak_month_report (" + weakMonthReportColumns + ") select "
				+ weakMonthReportColumns + " from weak_month_report where PROBLEM_ID in " + inClause;

		String sqlMoveAccidentCheckform3 = "insert into deleted_accident_checkform3 (" + accidentCheckformColumns + ") select "
				+ accidentCheckformColumns + " from accident_checkform3 where ID in " + inClause;
		String sqlMoveAccidentCheckform2 = "insert into deleted_accident_checkform2 (" + accidentCheckformColumns + ") select "
				+ accidentCheckformColumns + " from accident_checkform2 where ID in " + inClause;
		String sqlMoveAccidentCheckform1 = "insert into deleted_accident_checkform1 (" + accidentCheckformColumns + ") select "
				+ accidentCheckformColumns + " from accident_checkform1 where ID in " + inClause;
		String sqlMoveAccidentReport = "insert into deleted_accident_report (" + accidentReportColumns + " ) select "
				+ accidentReportColumns + " from accident_report where ID in " + inClause;
		String sqlMoveAccidentQuickReport = "insert into deleted_accident_quickreport (" + accidentQuickreportColumns + ") select "
				+ accidentQuickreportColumns + " from accident_quickreport where ID in " + inClause;
		String sqlMoveAccidentInfosheet = "insert into deleted_accident_infosheet (" + accidentInfosheetColumns + ") select "
				+ accidentInfosheetColumns + " from accident_infosheet where ID in " + inClause;
		String sqlMoveAccidentRegisterform = "insert into deleted_accident_registerform (" + accidentRegisterformColumns + ") select "
				+ accidentRegisterformColumns + " from accident_registerform where ID in " + inClause;
		String sqlMoveAccidentCasualty = "insert into deleted_accident_casualty (" + accidentCasualtyColumns + ") select "
				+ accidentCasualtyColumns + "from accident_casualty where ACCIDENT_ID in " + inClause;

		// Delete data
		String sqlDeleteAccidentCheckform3 = "delete from accident_checkform3 where ID in " + inClause;
		String sqlDeleteAccidentCheckform2 = "delete from accident_checkform2 where ID in " + inClause;
		String sqlDeleteAccidentCheckform1 = "delete from accident_checkform1 where ID in " + inClause;
		String sqlDeleteAccidentQuickreport = "delete from accident_quickreport where ID in " + inClause;
		String sqlDeleteAccidentReport = "delete from accident_report where ID in " + inClause;
		String sqlDeleteAccidentInfosheet = "delete from accident_infosheet where ID in " + inClause;
		String sqlDeleteAccidentRegisterform = "delete from accident_registerform where ID in " + inClause;
		String sqlDeleteAccidentCasualty = "delete from accident_casualty where ACCIDENT_ID in " + inClause;

		String sqlDeleteShutdown = "delete from  [shutdown] where ID in " + inClause;
		String sqlDeleteTendency = "delete from tendency where ID in " + inClause;
		String sqlDeleteDefect = "delete from defect where ID in " + inClause;
		String sqlDeleteOther = "delete from other where ID in " + inClause;

		String sqlDeleteWeakMonthReport = "delete from weak_month_report where PROBLEM_ID in " + inClause;
		String sqlDeletePostcheckSanweiReport = "delete from postcheck_month_sanwei_report where PROBLEM_ID in " + inClause;
		String sqlDeletePostcheckCheckReport = "delete from postcheck_month_check_report where PROBLEM_ID in " + inClause;
		String sqlDeleteHSEProblem = "delete from problem_hse_category where PROBLEM_ID in " + inClause;
		String sqlDeleteProblemTransferFeedBack = "delete from problem_transfer_feedback where PROBLEM_ID in " + inClause;
		String sqlDeleteProblemTransfer = "delete from problem_transfer where PROBLEM_ID in " + inClause;
		String sqlDeleteProblem = "delete from problem where ID in " + inClause;

		String[] sqls = new String[] { sqlMoveProblem, sqlMoveProblemTransfer, sqlMoveProblemTransferFeedBack, sqlMoveHSEProblem,
				sqlMovePostcheckCheckReport, sqlMovePostcheckSanweiReport, sqlMoveWeakMonthReport, sqlMoveDefect, sqlMoveOther,
				sqlMoveTendency, sqlMoveShutdown, sqlMoveAccidentCheckform3, sqlMoveAccidentCheckform2, sqlMoveAccidentCheckform1,
				sqlMoveAccidentReport, sqlMoveAccidentQuickReport, sqlMoveAccidentInfosheet, sqlMoveAccidentRegisterform,
				sqlMoveAccidentCasualty, sqlDeleteAccidentCasualty, sqlDeleteAccidentCheckform3, sqlDeleteAccidentCheckform2,
				sqlDeleteAccidentCheckform1, sqlDeleteAccidentReport, sqlDeleteAccidentQuickreport, sqlDeleteAccidentInfosheet,
				sqlDeleteAccidentRegisterform, sqlDeleteShutdown, sqlDeleteTendency, sqlDeleteDefect, sqlDeleteOther,
				sqlDeleteWeakMonthReport, sqlDeletePostcheckSanweiReport, sqlDeletePostcheckCheckReport, sqlDeleteHSEProblem,
				sqlDeleteProblemTransferFeedBack, sqlDeleteProblemTransfer, sqlDeleteProblem };

		Connection conn = null;
		try {
			conn = ConnectionManager.getSafeManageConn();

			conn.setAutoCommit(false);

			Statement stmt = conn.createStatement();
			for (int i = 0; i < sqls.length; i++) {
				stmt.addBatch(sqls[i]);
			}

			int[] rtn = stmt.executeBatch();
			System.out.println();
			System.out.print("Deleting problem:");
			for (int i : rtn) {
				System.out.print(i + ",");
			}

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean updateProblemTransfer(Long problemID, boolean isProcessed) {
		String sql = "update problem_transfer set IS_PROCESSED = ? where PROBLEM_ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setBoolean(1, isProcessed);
			pstm.setLong(2, problemID);

			conn.setAutoCommit(false);
			conn.commit();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	/**
	 * value cannot be null
	 * 
	 * @param problemID
	 * @param fieldName
	 * @param value
	 * @return
	 */
	public boolean updateProblem(Long problemID, String fieldName, Object value) {
		if (value != null) {
			return updateProblem(problemID, fieldName, value, value.getClass());
		} else {
			return false;
		}
	}

	public boolean updateProblem(Long problemID, String fieldName, Object value, Class clazz) {
		String sql = "update problem set " + fieldName + "= ? where ID = ?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			if (clazz.equals(String.class)) {
				if (value == null) {
					pstm.setNull(1, Types.VARCHAR);
				} else {
					pstm.setString(1, (String) value);
				}
			} else if (clazz.equals(Long.class)) {
				if (value == null) {
					pstm.setNull(1, Types.INTEGER);
				} else {
					pstm.setLong(1, (Long) value);
				}
			} else if (clazz.equals(Integer.class)) {
				if (value == null) {
					pstm.setNull(1, Types.INTEGER);
				} else {
					pstm.setLong(1, (Integer) value);
				}
			} else if (clazz.equals(Boolean.class)) {
				if (value == null) {
					pstm.setNull(1, Types.BOOLEAN);
				} else {
					pstm.setBoolean(1, (Boolean) value);
				}
			} else if (clazz.equals(Calendar.class)) {
				if (value == null) {
					pstm.setNull(1, Types.TIMESTAMP);
				} else {
					pstm.setTimestamp(1, new Timestamp(((Calendar) value).getTimeInMillis()));
				}
			}

			pstm.setLong(2, problemID);
			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean updateProblem(Long problemID, Integer problemType, String problemTitle, String problemContent, String problemZhenggai,
			Integer grade, Long postcheckID, Long postcheckPatternID, Long postcheckContentID, Long postcheckStandardID,
			Calendar completDate, int processStatus) {
		String sql = "update problem set PROBLEM_TYPE = ?, GRADE=?, POSTCHECK_ID = ?, POSTCHECK_PATTERN_ID=?, "
				+ " POSTCHECK_CONTENT_ID=?, POSTCHECK_STANDARD_ID=?, COMPLETE_DATE=?, PROCESS_STATUS=?, PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI =?, "
				+ " CLOSE_TIME = null, CLOSE_USER_ID = null " + " where ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			conn.setAutoCommit(false);

			if (problemType == null) {
				pstm.setNull(1, Types.INTEGER);
			} else {
				pstm.setInt(1, problemType);
			}

			if (grade == null) {
				pstm.setNull(2, Types.INTEGER);
			} else {
				pstm.setInt(2, grade);
			}

			if (postcheckID == null) {
				pstm.setNull(3, Types.BIGINT);
			} else {
				pstm.setLong(3, postcheckID);
			}

			if (postcheckPatternID == null) {
				pstm.setNull(4, Types.BIGINT);
			} else {
				pstm.setLong(4, postcheckPatternID);
			}

			if (postcheckContentID == null) {
				pstm.setNull(5, Types.BIGINT);
			} else {
				pstm.setLong(5, postcheckContentID);
			}

			if (postcheckStandardID == null) {
				pstm.setNull(6, Types.BIGINT);
			} else {
				pstm.setLong(6, postcheckStandardID);
			}

			if (completDate == null) {
				pstm.setNull(7, Types.DATE);
			} else {
				pstm.setTimestamp(7, new Timestamp(completDate.getTimeInMillis()));
			}

			pstm.setInt(8, processStatus);

			pstm.setString(9, problemTitle);
			pstm.setString(10, problemContent);
			pstm.setString(11, problemZhenggai);

			if (problemID == null) {
				pstm.setNull(12, Types.BIGINT);
			} else {
				pstm.setLong(12, problemID);
			}

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public List<DefectType> getDefectTypeList() {
		String sql = "select ID, DEFECT_TYPE, DEPARTMENT1_ID from defect_type order by ID ";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<DefectType> lst = new ArrayList<DefectType>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				DefectType type = new DefectType();
				type.setId(rs.getLong("ID"));
				type.setDefectType(rs.getString("DEFECT_TYPE"));
				type.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));

				lst.add(type);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public List<ShutdownType> getShutdownTypeList(boolean isDeleted) {
		String sql = "select ID, SHUTDOWN_TYPE, DEPARTMENT1_ID from shutdown_type where IS_DELETED=? order by ID ";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<ShutdownType> lst = new ArrayList<ShutdownType>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setBoolean(1, isDeleted);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ShutdownType type = new ShutdownType();
				type.setId(rs.getLong("ID"));
				type.setShutdownType(rs.getString("SHUTDOWN_TYPE"));
				type.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));

				lst.add(type);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public List<ShutdownType> getShutdownTypeList() {
		String sql = "select ID, SHUTDOWN_TYPE, DEPARTMENT1_ID from shutdown_type order by ID ";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<ShutdownType> lst = new ArrayList<ShutdownType>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ShutdownType type = new ShutdownType();
				type.setId(rs.getLong("ID"));
				type.setShutdownType(rs.getString("SHUTDOWN_TYPE"));
				type.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));

				lst.add(type);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public List<TendencyType> getTendencyTypeList() {
		String sql = "select ID, TENDENCY_TYPE, DEPARTMENT1_ID from TENDENCY_type order by ID ";

		Connection conn = null;
		PreparedStatement pstm = null;
		List<TendencyType> lst = new ArrayList<TendencyType>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				TendencyType type = new TendencyType();
				type.setId(rs.getLong("ID"));
				type.setTendencyType(rs.getString("TENDENCY_TYPE"));
				type.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));

				lst.add(type);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return lst;
	}

	public boolean convertDefect(Long problemID, Integer defectTypeID, Calendar writeDate, String problemTitle, String problemContent,
			String loss, String reason, String zhenggaiCuoshi, String duty, String team, Long defectWriteUserID) {
		String updateProblemSQL = "update problem set PROBLEM_TYPE=?, PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=?, "
				+ " CLOSE_USER_ID=?, CLOSE_TIME=? where ID = ?";

		String insertDefectSQL = "insert into defect(ID, DEFECT_TYPE_ID, LOSS, REASON, DUTY, TEAM, DEFECT_TIME, DEFECT_WRITE_TIME, DEFECT_WRITE_USER_ID) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmInsert = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmInsert = conn.prepareStatement(insertDefectSQL);

			conn.setAutoCommit(false);
			pstmUpdate.setInt(1, Constants.PROBLEM_TYPE_DEFECT);
			pstmUpdate.setString(2, problemTitle);
			pstmUpdate.setString(3, problemContent);
			pstmUpdate.setString(4, zhenggaiCuoshi);

			pstmUpdate.setNull(5, Types.BIGINT);
			pstmUpdate.setNull(6, Types.TIMESTAMP);
			pstmUpdate.setLong(7, problemID);

			pstmUpdate.executeUpdate();

			pstmInsert.setLong(1, problemID);
			pstmInsert.setInt(2, defectTypeID);
			pstmInsert.setString(3, loss);
			pstmInsert.setString(4, reason);
			pstmInsert.setString(5, duty);
			pstmInsert.setString(6, team);
			if (writeDate != null) {
				pstmInsert.setTimestamp(7, new Timestamp(writeDate.getTimeInMillis()));
			} else {
				pstmInsert.setNull(7, Types.TIMESTAMP);
			}
			pstmInsert.setTimestamp(8, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			pstmInsert.setLong(9, defectWriteUserID);

			pstmInsert.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

	public boolean convertShutdown(Long problemID, Integer problemType, Integer shutdownTypeID, String problemTitle, String problemContent,
			String loss, String reason, String zhenggaiCuoshi, String duty, String team, Calendar periodStart, Calendar periodEnd,
			Long shutdownWriteUserID, Boolean escalateZongbu) {
		String updateProblemSQL = "update problem set PROBLEM_TYPE=?, PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=?, "
				+ " CLOSE_USER_ID=?, CLOSE_TIME=? where ID = ?";

		String insertShutdownSQL = "insert into  [shutdown](ID, SHUTDOWN_TYPE_ID, LOSS, REASON, DUTY, TEAM, PERIOD_START, PERIOD_END, SHUTDOWN_WRITE_TIME, SHUTDOWN_WRITE_USER_ID, ESCALATE_ZONGBU)"
				+ " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmInsert = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmInsert = conn.prepareStatement(insertShutdownSQL);

			conn.setAutoCommit(false);
			pstmUpdate.setInt(1, problemType);
			pstmUpdate.setString(2, problemTitle);
			pstmUpdate.setString(3, problemContent);
			pstmUpdate.setString(4, zhenggaiCuoshi);

			pstmUpdate.setNull(5, Types.BIGINT);
			pstmUpdate.setNull(6, Types.TIMESTAMP);
			pstmUpdate.setLong(7, problemID);

			pstmUpdate.executeUpdate();

			pstmInsert.setLong(1, problemID);
			pstmInsert.setInt(2, shutdownTypeID);
			pstmInsert.setString(3, loss);
			pstmInsert.setString(4, reason);
			pstmInsert.setString(5, duty);
			pstmInsert.setString(6, team);
			pstmInsert.setTimestamp(7, new Timestamp(periodStart.getTimeInMillis()));
			pstmInsert.setTimestamp(8, new Timestamp(periodEnd.getTimeInMillis()));
			pstmInsert.setTimestamp(9, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			pstmInsert.setLong(10, shutdownWriteUserID);
			if (escalateZongbu == null) {
				pstmInsert.setNull(11, Types.BOOLEAN);
			} else {
				pstmInsert.setBoolean(11, escalateZongbu);
			}

			pstmInsert.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

	public boolean updateDefect(Long problemID, String problemTitle, String problemContent, String loss, String reason,
			String zhenggaiCuoshi, String duty, String team) {
		String updateProblemSQL = "update problem set PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=? " + " where ID = ?";
		String updateDefectSQL = "update defect set LOSS=? , REASON = ?, DUTY=?, TEAM=? where ID = ?";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmDefect = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmDefect = conn.prepareStatement(updateDefectSQL);

			conn.setAutoCommit(false);
			pstmUpdate.setString(1, problemTitle);
			pstmUpdate.setString(2, problemContent);
			pstmUpdate.setString(3, zhenggaiCuoshi);
			pstmUpdate.setLong(4, problemID);

			pstmUpdate.executeUpdate();

			pstmDefect.setString(1, loss);
			pstmDefect.setString(2, reason);
			pstmDefect.setString(3, duty);
			pstmDefect.setString(4, team);
			pstmDefect.setLong(5, problemID);

			pstmDefect.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

	public boolean updateShutdown(Long problemID, String problemTitle, String problemContent, String loss, String reason,
			String zhenggaiCuoshi, String duty, String team, Calendar periodStart, Calendar periodEnd) {
		String updateProblemSQL = "update problem set PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=? " + " where ID = ?";
		String updateShutdownSQL = "update  [shutdown] set LOSS=? , REASON = ?, DUTY=?, TEAM=?, PERIOD_START=?, PERIOD_END=? where ID = ?";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmShutdown = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmShutdown = conn.prepareStatement(updateShutdownSQL);

			conn.setAutoCommit(false);
			pstmUpdate.setString(1, problemTitle);
			pstmUpdate.setString(2, problemContent);
			pstmUpdate.setString(3, zhenggaiCuoshi);
			pstmUpdate.setLong(4, problemID);

			pstmUpdate.executeUpdate();

			pstmShutdown.setString(1, loss);
			pstmShutdown.setString(2, reason);
			pstmShutdown.setString(3, duty);
			pstmShutdown.setString(4, team);
			pstmShutdown.setTimestamp(5, new Timestamp(periodStart.getTimeInMillis()));
			pstmShutdown.setTimestamp(6, new Timestamp(periodEnd.getTimeInMillis()));
			pstmShutdown.setLong(7, problemID);

			pstmShutdown.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

	public boolean updateTendency(Long problemID, String problemTitle, String problemContent, String loss, String reason,
			String zhenggaiCuoshi, String duty, String team) {
		String updateProblemSQL = "update problem set PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=? " + " where ID = ?";
		String updateTendencySQL = "update tendency set LOSS=? , REASON = ?, DUTY=?, TEAM=? where ID = ?";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmTendency = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmTendency = conn.prepareStatement(updateTendencySQL);

			conn.setAutoCommit(false);
			pstmUpdate.setString(1, problemTitle);
			pstmUpdate.setString(2, problemContent);
			pstmUpdate.setString(3, zhenggaiCuoshi);
			pstmUpdate.setLong(4, problemID);

			pstmUpdate.executeUpdate();

			pstmTendency.setString(1, loss);
			pstmTendency.setString(2, reason);
			pstmTendency.setString(3, duty);
			pstmTendency.setString(4, team);
			pstmTendency.setLong(5, problemID);

			pstmTendency.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

	public boolean convertTendency(Long problemID, Integer tendencyTypeID, Calendar writeDate, String problemTitle, String problemContent,
			String loss, String reason, String zhenggaiCuoshi, String duty, String team, Long tendencyWriteUserID) {
		String updateProblemSQL = "update problem set PROBLEM_TYPE=?, PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=?, "
				+ " CLOSE_USER_ID=?, CLOSE_TIME=? where ID = ?";
		String insertTendencySQL = "insert into tendency(ID, TENDENCY_TYPE_ID, LOSS, REASON, DUTY, TEAM, TENDENCY_TIME, TENDENCY_WRITE_TIME, TENDENCY_WRITE_USER_ID) "
				+ " values(?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmInsert = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmInsert = conn.prepareStatement(insertTendencySQL);

			conn.setAutoCommit(false);
			pstmUpdate.setInt(1, Constants.PROBLEM_TYPE_TENDENCY);
			pstmUpdate.setString(2, problemTitle);
			pstmUpdate.setString(3, problemContent);
			pstmUpdate.setString(4, zhenggaiCuoshi);

			pstmUpdate.setNull(5, Types.BIGINT);
			pstmUpdate.setNull(6, Types.TIMESTAMP);
			pstmUpdate.setLong(7, problemID);

			pstmUpdate.executeUpdate();

			pstmInsert.setLong(1, problemID);
			pstmInsert.setInt(2, tendencyTypeID);
			pstmInsert.setString(3, loss);
			pstmInsert.setString(4, reason);
			pstmInsert.setString(5, duty);
			pstmInsert.setString(6, team);
			if (writeDate != null) {
				pstmInsert.setTimestamp(7, new Timestamp(writeDate.getTimeInMillis()));
			} else {
				pstmInsert.setNull(7, Types.TIMESTAMP);
			}
			pstmInsert.setTimestamp(8, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			pstmInsert.setLong(9, tendencyWriteUserID);

			pstmInsert.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

	public int countDepartmentProblemNum(Long depart1ID, Boolean isZhenggai, Boolean isProcessingByChushi, String dateStart, String dateEnd) {
		String sql = "select COUNT(*) as DEPART1_PROBLEM_COUNT from problem where DEPARTMENT1_ID=? ";

		if (isZhenggai != null) {
			if (isZhenggai) {
				sql += "and PROCESS_STATUS<" + Constants.PROBLEM_STATUS_CLOSE + " ";
			} else {
				sql += "and PROCESS_STATUS>=" + Constants.PROBLEM_STATUS_CLOSE + " ";
			}
		}

		if (isProcessingByChushi != null) {
			if (isProcessingByChushi) {
				sql += "and (PROCESSING_BY_CHUSHI=1 or ESCALATE_DEPART1_ID is not null) ";
			} else {
				sql += "and (PROCESSING_BY_CHUSHI=0 or PROCESSING_BY_CHUSHI is null or ESCALATE_DEPART1_ID is null) ";
			}
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			sql += "and WRITE_TIME>='" + dateStart + " 00:00:00' ";
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			sql += "and WRITE_TIME<='" + dateEnd + " 23:59:59' ";
		}

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				int num = rs.getInt("DEPART1_PROBLEM_COUNT");
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return 0;
	}

	public int countEquipmentProblemNum(Long equipID, Boolean isZhenggai, Boolean isProcessingByChushi) {
		String sql = "select COUNT(*) as EQUIP_PROBLEM_COUNT from problem where EQUIPMENT_ID=?";

		if (isZhenggai != null) {
			if (isZhenggai) {
				sql += " and PROCESS_STATUS<" + Constants.PROBLEM_STATUS_CLOSE;
			} else {
				sql += " and PROCESS_STATUS>=" + Constants.PROBLEM_STATUS_CLOSE;
			}
		}

		if (isProcessingByChushi != null) {
			if (isProcessingByChushi) {
				sql += " and (PROCESSING_BY_CHUSHI=1 or ESCALATE_DEPART1_ID is not null)";
			} else {
				sql += " and (PROCESSING_BY_CHUSHI=0 or PROCESSING_BY_CHUSHI is null or ESCALATE_DEPART1_ID is null)";
			}
		}

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, equipID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				int num = rs.getInt("EQUIP_PROBLEM_COUNT");
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return 0;
	}

	public Map<String, Object> searchDuchayuanProblems(String dateStart, String dateEnd, int roleLevel, int page, int pageSize) {
		String sql = "select problem.ID, problem.WRITE_USER_ID, employee.NAME, problem.WRITE_TIME, problem.EQUIPMENT_ID, equipment.EQUIP_NAME, "
				+ "problem.PROBLEM_TITLE, problem.PROBLEM_CONTENT, problem.PROBLEM_ZHENGGAI_CUOSHI, problem.CLOSE_TIME, role.ROLE_LEVEL, role.ROLE_TYPE, "
				+ "department1.DEPARTMENT1_NAME from problem "
				+ "inner join employee on problem.WRITE_USER_ID=employee.ID "
				+ "inner join equipment on problem.EQUIPMENT_ID=equipment.ID "
				+ "inner join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join role on department3.ROLE_ID=role.ID "
				+ "where role.ROLE_LEVEL>=? and problem.PROBLEM_TYPE is null ";

		boolean hasPre = true;

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		int totalNum = DaoUtil.getCount(sql, new int[] { Types.INTEGER }, new Object[] { roleLevel });
		sql = DaoUtil.getPagingSQL(sql, page, pageSize, new String[] { "ROLE_LEVEL", "ROLE_TYPE", "NAME", "WRITE_TIME" }, new Boolean[] {
				Boolean.FALSE, Boolean.FALSE, Boolean.TRUE, Boolean.FALSE });

		List<Problem> problemList = new ArrayList<Problem>();

		Map<String, Object> resultMap = new HashMap<String, Object>(2);
		resultMap.put(Constants.DAO_RESULT, problemList);
		resultMap.put(Constants.DAO_RESULT_NUMBER, totalNum);

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, roleLevel);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(ValueUtil.getLong(rs.getObject("ID")));
				problem.setWriterDepartment1Name(ValueUtil.getString(rs.getObject("DEPARTMENT1_NAME")));
				problem.setWriteUserID(ValueUtil.getLong(rs.getObject("WRITE_USER_ID")));
				problem.setWriteUserName(ValueUtil.getString(rs.getObject("NAME")));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setEquipmentID(ValueUtil.getLong(rs.getObject("EQUIPMENT_ID")));
				problem.setEquipmentName(ValueUtil.getString(rs.getObject("EQUIP_NAME")));
				problem.setProblemTitle(ValueUtil.getString(rs.getObject("PROBLEM_TITLE")));
				problem.setProblemContent(ValueUtil.getString(rs.getObject("PROBLEM_CONTENT")));
				problem.setProblemZhenggai(ValueUtil.getString(rs.getObject("PROBLEM_ZHENGGAI_CUOSHI")));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public List<ProblemDepartStatisticItem> getProblemDepartStatistics(Long depart1ID, String dateStart, String dateEnd, int statusMin,
			int statusMax) {
		String sql = "select PROBLEM_COUNT, WRITE_USER_ID, employee.NAME, ROLE_ID, ROLE_TYPE from employee inner join "
				+ "(select COUNT(*) as PROBLEM_COUNT, WRITE_USER_ID from problem "
				+ "where WRITE_USER_DEPARTMENT1_ID=? and problem.WRITE_TIME>=? and problem.WRITE_TIME<=? and problem.PROCESS_STATUS>=? and problem.PROCESS_STATUS<=? "
				+ "group by WRITE_USER_ID) as t " + "on t.WRITE_USER_ID=employee.ID "
				+ "inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join [role] on [role].ID=department3.ROLE_ID " + "order by ROLE_ID desc";

		List<ProblemDepartStatisticItem> itemList = new ArrayList<ProblemDepartStatisticItem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);
			if (!Util.isNullOrEmpty(dateStart)) {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime(dateStart + " 00:00:00").getTimeInMillis()));
			} else {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime("1980-01-01 00:00:00").getTimeInMillis()));
			}
			if (!Util.isNullOrEmpty(dateEnd)) {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime(dateEnd + " 23:59:59").getTimeInMillis()));
			} else {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime("2199-01-01 23:59:59").getTimeInMillis()));
			}
			if (statusMin > 0) {
				pstm.setInt(4, statusMin);
			} else {
				pstm.setInt(4, 0);
			}
			if (statusMax > 0) {
				pstm.setInt(5, statusMax);
			} else {
				pstm.setInt(5, Integer.MAX_VALUE);
			}

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProblemDepartStatisticItem item = new ProblemDepartStatisticItem();
				item.setWriteProblemCount(ValueUtil.getInteger(rs.getObject("PROBLEM_COUNT")));
				item.setWriteUserID(ValueUtil.getLong(rs.getObject("WRITE_USER_ID")));
				item.setWriterName(ValueUtil.getString(rs.getObject("NAME")));
				item.setRoleID(ValueUtil.getLong(rs.getObject("ROLE_ID")));
				item.setRoleType(ValueUtil.getInteger(rs.getObject("ROLE_TYPE")));

				itemList.add(item);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return itemList;
	}

	public List<ProblemDepartStatisticItem> getUsersTransferedProblemCount(Long depart1ID, String dateStart, String dateEnd) {
		String sql = "select employee.ID, employee.NAME, t.PROBLEM_COUNT, [role].ID as ROLE_ID, [role].ROLE_TYPE from employee "
				+ "inner join (select USER_ID, count(distinct PROBLEM_ID) as PROBLEM_COUNT from problem_transfer "
				+ "inner join problem on problem_transfer.PROBLEM_ID=problem.ID "
				+ "where USER_ID in (select ID from employee where DEPARTMENT1_ID=?) and WRITE_TIME>=? and WRITE_TIME<=? "
				+ "group by USER_ID) as t on employee.ID=t.USER_ID " + "inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join [role] on department3.ROLE_ID=[role].ID where employee.DEPARTMENT1_ID=? order by ROLE_TYPE desc";

		List<ProblemDepartStatisticItem> itemList = new ArrayList<ProblemDepartStatisticItem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);
			if (!Util.isNullOrEmpty(dateStart)) {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime(dateStart + " 00:00:00").getTimeInMillis()));
			} else {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime("1980-01-01 00:00:00").getTimeInMillis()));
			}
			if (!Util.isNullOrEmpty(dateEnd)) {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime(dateEnd + " 23:59:59").getTimeInMillis()));
			} else {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime("2199-01-01 23:59:59").getTimeInMillis()));
			}
			pstm.setLong(4, depart1ID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProblemDepartStatisticItem item = new ProblemDepartStatisticItem();
				item.setProcessedProblemCount(ValueUtil.getInteger(rs.getObject("PROBLEM_COUNT")));
				item.setWriteUserID(ValueUtil.getLong(rs.getObject("ID")));
				item.setWriterName(ValueUtil.getString(rs.getObject("NAME")));
				item.setRoleID(ValueUtil.getLong(rs.getObject("ROLE_ID")));
				item.setRoleType(ValueUtil.getInteger(rs.getObject("ROLE_TYPE")));

				itemList.add(item);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return itemList;
	}

	public List<Problem> getUserTransferedProblemList(Long userID, String dateStart, String dateEnd) {
		String sql = "select problem.ID, department1.DEPARTMENT1_NAME, department2.DEPARTMENT2_NAME, department3.DEPARTMENT3_NAME, WRITE_USER_ID, "
				+ "employee.NAME, PROBLEM_TITLE, PROBLEM_CONTENT, WRITE_TIME, CLOSE_TIME, equipment.EQUIP_NAME "
				+ "from problem inner join employee on WRITE_USER_ID=employee.ID "
				+ "inner join department1 on employee.DEPARTMENT1_ID=department1.ID "
				+ "inner join department2 on employee.DEPARTMENT2_ID=department2.ID "
				+ "inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join equipment on problem.EQUIPMENT_ID=equipment.ID "
				+ "inner join [role] on department3.ROLE_ID=[role].ID "
				+ "where problem.ID in (select distinct PROBLEM_ID from problem_transfer where USER_ID=?) and problem.WRITE_TIME>=? and problem.WRITE_TIME<=? "
				+ "order by [role].ROLE_LEVEL desc, employee.NAME";

		List<Problem> problemList = new ArrayList<Problem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);
			if (!Util.isNullOrEmpty(dateStart)) {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime(dateStart + " 00:00:00").getTimeInMillis()));
			} else {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime("1980-01-01 00:00:00").getTimeInMillis()));
			}
			if (!Util.isNullOrEmpty(dateEnd)) {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime(dateEnd + " 23:59:59").getTimeInMillis()));
			} else {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime("2199-01-01 23:59:59").getTimeInMillis()));
			}

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(ValueUtil.getLong(rs.getObject("ID")));
				problem.setWriterDepartment1Name(ValueUtil.getString(rs.getObject("DEPARTMENT1_NAME")));
				problem.setWriterDepartment2Name(ValueUtil.getString(rs.getObject("DEPARTMENT2_NAME")));
				problem.setWriterDepartment3Name(ValueUtil.getString(rs.getObject("DEPARTMENT3_NAME")));
				problem.setWriteUserName(ValueUtil.getString(rs.getObject("NAME")));
				problem.setProblemTitle(ValueUtil.getString(rs.getObject("PROBLEM_TITLE")));
				problem.setProblemContent(ValueUtil.getString(rs.getObject("PROBLEM_CONTENT")));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setEquipmentName(ValueUtil.getString(rs.getObject("EQUIP_NAME")));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public List<Problem> getUserWriteProblemList(Long userID, String dateStart, String dateEnd) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, problem.WRITE_USER_ID, employee.NAME, WRITE_TIME, CLOSE_TIME, "
				+ "EQUIPMENT_ID, department1.DEPARTMENT1_NAME, department2.DEPARTMENT2_NAME, department3.DEPARTMENT3_NAME, equipment.EQUIP_NAME "
				+ "from problem inner join employee on problem.WRITE_USER_ID=employee.ID "
				+ "inner join department1 on employee.DEPARTMENT1_ID=department1.ID "
				+ "inner join department2 on employee.DEPARTMENT2_ID=department2.ID "
				+ "inner join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "inner join equipment on problem.EQUIPMENT_ID=equipment.ID "
				+ "where WRITE_USER_ID=? and problem.WRITE_TIME>=? and problem.WRITE_TIME<=?";

		List<Problem> problemList = new ArrayList<Problem>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);
			if (!Util.isNullOrEmpty(dateStart)) {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime(dateStart + " 00:00:00").getTimeInMillis()));
			} else {
				pstm.setTimestamp(2, new Timestamp(Util.convertStrToDateTime("1980-01-01 00:00:00").getTimeInMillis()));
			}
			if (!Util.isNullOrEmpty(dateEnd)) {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime(dateEnd + " 23:59:59").getTimeInMillis()));
			} else {
				pstm.setTimestamp(3, new Timestamp(Util.convertStrToDateTime("2199-01-01 23:59:59").getTimeInMillis()));
			}

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(ValueUtil.getLong(rs.getObject("ID")));
				problem.setWriterDepartment1Name(ValueUtil.getString(rs.getObject("DEPARTMENT1_NAME")));
				problem.setWriterDepartment2Name(ValueUtil.getString(rs.getObject("DEPARTMENT2_NAME")));
				problem.setWriterDepartment3Name(ValueUtil.getString(rs.getObject("DEPARTMENT3_NAME")));
				problem.setWriteUserName(ValueUtil.getString(rs.getObject("NAME")));
				problem.setProblemTitle(ValueUtil.getString(rs.getObject("PROBLEM_TITLE")));
				problem.setProblemContent(ValueUtil.getString(rs.getObject("PROBLEM_CONTENT")));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				problem.setEquipmentName(ValueUtil.getString(rs.getObject("EQUIP_NAME")));

				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return problemList;
	}

	public boolean addOther(Long problemID, Integer defectTypeID, Calendar writeDate, String problemTitle, String problemContent,
			String loss, String reason, String zhenggaiCuoshi, String duty, String team, Long defectWriteUserID) {
		String updateProblemSQL = "update problem set PROBLEM_TYPE=?, PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=?, "
				+ " CLOSE_USER_ID=?, CLOSE_TIME=? where ID = ?";

		String insertDefectSQL = "insert into other(ID, DEFECT_TYPE_ID, LOSS, REASON, DUTY, TEAM, DEFECT_TIME, DEFECT_WRITE_TIME, DEFECT_WRITE_USER_ID) "
				+ "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmInsert = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmInsert = conn.prepareStatement(insertDefectSQL);

			conn.setAutoCommit(false);
			pstmUpdate.setInt(1, Constants.PROBLEM_TYPE_OTHER);
			pstmUpdate.setString(2, problemTitle);
			pstmUpdate.setString(3, problemContent);
			pstmUpdate.setString(4, zhenggaiCuoshi);

			pstmUpdate.setNull(5, Types.BIGINT);
			pstmUpdate.setNull(6, Types.TIMESTAMP);
			pstmUpdate.setLong(7, problemID);

			pstmUpdate.executeUpdate();

			pstmInsert.setLong(1, problemID);
			pstmInsert.setInt(2, defectTypeID);
			pstmInsert.setString(3, loss);
			pstmInsert.setString(4, reason);
			pstmInsert.setString(5, duty);
			pstmInsert.setString(6, team);
			if (writeDate != null) {
				pstmInsert.setTimestamp(7, new Timestamp(writeDate.getTimeInMillis()));
			} else {
				pstmInsert.setNull(7, Types.TIMESTAMP);
			}
			pstmInsert.setTimestamp(8, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			pstmInsert.setLong(9, defectWriteUserID);

			pstmInsert.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

	public Map<String, Object> searchOthers(Long department1ID, Long equipmentID, Long defectTypeID, String problemTitle,
			String problemContent, String dateStart, String dateEnd, int page) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, "
				+ "WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME, "
				+ " other.DEFECT_TYPE_ID, other.LOSS, other.REASON, dt.DEFECT_TYPE "
				+ "from problem left outer join employee on problem.WRITE_USER_ID=employee.ID "
				+ "left outer join department1 on problem.DEPARTMENT1_ID=department1.ID "
				+ "left outer join equipment on problem.EQUIPMENT_ID=equipment.ID " + " left outer join other on other.ID = problem.ID "
				+ "left outer join defect_type as dt on dt.ID = other.DEFECT_TYPE_ID " + " where PROBLEM_TYPE = "
				+ Constants.PROBLEM_TYPE_OTHER + " and PROCESS_STATUS >= " + Constants.PROBLEM_STATUS_CLOSE + " ";

		boolean hasPre = true;

		if (department1ID != null && department1ID > 0) {
			if (hasPre) {
				sql += " and ";
			}
			sql += "problem.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}

		if (equipmentID != null && equipmentID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}

		if (defectTypeID != null && defectTypeID > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "other.DEFECT_TYPE_ID=" + defectTypeID + " ";
			hasPre = true;
		}

		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}

		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}

		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}

		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}

		Map<String, Object> resultMap = new HashMap<String, Object>();
		resultMap.put(Constants.DAO_RESULT_NUMBER, DaoUtil.getCount(sql, new int[] {}, new Object[] {}));
		sql = DaoUtil.getPagingSQL(sql, page, Constants.PAGE_SIZE, "WRITE_TIME desc, ID ", false);

		List<Other> problemList = new ArrayList<Other>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Other problem = new Other();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));

				problem.setDefectTypeID(rs.getInt("DEFECT_TYPE_ID"));
				problem.setLoss(rs.getString("LOSS"));
				problem.setReason(rs.getString("REASON"));

				problem.setDefectType(rs.getString("DEFECT_TYPE"));

				problemList.add(problem);
			}

			resultMap.put(Constants.DAO_RESULT, problemList);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return resultMap;
	}

	public Other getOtherByID(Long problemID) {
		Problem problem = getProblemByID(problemID);

		String sql = " select d.ID, DEFECT_TYPE_ID, LOSS, REASON, DEFECT_TYPE, DUTY, TEAM, DEFECT_TIME, DEFECT_WRITE_TIME, DEFECT_WRITE_USER_ID "
				+ "from other as d left outer join defect_type as dt on dt.ID = d.DEFECT_TYPE_ID where d.ID = ? ";

		Connection conn = null;
		PreparedStatement pstm = null;

		Other other = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, problemID);

			ResultSet rs = pstm.executeQuery();

			if (rs.next()) {
				other = new Other(problem);
				other.setId(rs.getLong("ID"));
				other.setDefectTypeID(rs.getInt("DEFECT_TYPE_ID"));
				other.setDefectType(rs.getString("DEFECT_TYPE"));
				other.setLoss(rs.getString("LOSS"));
				other.setReason(rs.getString("REASON"));
				other.setDuty(rs.getString("DUTY"));
				other.setTeam(rs.getString("TEAM"));
				other.setDefectTime(ValueUtil.getCalendar(rs.getObject("DEFECT_TIME")));
				other.setDefectWriteTime(ValueUtil.getCalendar(rs.getObject("DEFECT_WRITE_TIME")));
				Object obj = rs.getObject("DEFECT_WRITE_USER_ID");
				if (obj != null) {
					other.setDefectWriteUserID(Long.valueOf((Integer) obj));
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return other;
	}

	public boolean updateOther(Long problemID, String problemTitle, String problemContent, String loss, String reason,
			String zhenggaiCuoshi, String duty, String team) {
		String updateProblemSQL = "update problem set PROBLEM_TITLE=?, PROBLEM_CONTENT=?, PROBLEM_ZHENGGAI_CUOSHI=? " + " where ID = ?";
		String updateOtherSQL = "update other set LOSS=? , REASON = ?, DUTY=?, TEAM=? where ID = ?";

		Connection conn = null;
		PreparedStatement pstmUpdate = null;
		PreparedStatement pstmOther = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstmUpdate = conn.prepareStatement(updateProblemSQL);
			pstmOther = conn.prepareStatement(updateOtherSQL);

			conn.setAutoCommit(false);
			pstmUpdate.setString(1, problemTitle);
			pstmUpdate.setString(2, problemContent);
			pstmUpdate.setString(3, zhenggaiCuoshi);
			pstmUpdate.setLong(4, problemID);

			pstmUpdate.executeUpdate();

			pstmOther.setString(1, loss);
			pstmOther.setString(2, reason);
			pstmOther.setString(3, duty);
			pstmOther.setString(4, team);
			pstmOther.setLong(5, problemID);

			pstmOther.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			return false;
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return true;
	}

}
